 |
|
| SAS Names and Support for DBMS Names |
SAS/ACCESS Naming Examples
| Replacing Unsupported Characters |
In the following example, a view, myview, is created from the Oracle table, mytable.
proc sql; connect to oracle (user=testuser password=testpass); create view myview as select * from connection to oracle (select "Amount Budgeted$", "Amount Spent$" from mytable); quit; proc contents data=myview; run;
In the output produced by PROC CONTENTS, the Oracle column names (that were processed by the SQL view of MYTABLE) are renamed to different SAS variable names: Amount Budgeted$ becomes Amount_Budgeted_ and Amount Spent$ becomes Amount_Spent_.
| Preserving Column Names |
The following example uses the Oracle table, PAYROLL, to create a new Oracle table, PAY1, and then prints the table. Both the PRESERVE_COL_NAMES=YES and the PROC SQL DQUOTE=ANSI options are used to preserve the case and nonstandard characters in the column names. You do not need to quote the column aliases in order to preserve the mixed case. You only need double quotation marks when the column name has nonstandard characters or blanks.
By default, most SAS/ACCESS interfaces use DBMS-specific rules to set the case of table and column names. Therefore, even though the new pay1 Oracle table name is created in lowercase in this example, Oracle stores the name in uppercase as PAY1. If you want the table name to be stored as "pay1", you must set PRESERVE_TAB_NAMES=NO.
options linesize=120 pagesize=60 nodate; libname mydblib oracle user=testuser password=testpass path='ora8_servr' schema=hrdept preserve_col_names=yes; proc sql dquote=ansi; create table mydblib.pay1 as select idnum as "ID #", sex, jobcode, salary, birth as BirthDate, hired as HiredDate from mydblib.payroll order by birth; title "Payroll Table with Revised Column Names"; select * from mydblib.pay1; quit;
SAS recognizes the JOBCODE, SEX, and SALARY column names, whether you specify them in your SAS code as lowercase, mixed case, or uppercase. In the Oracle table, PAYROLL, the SEX, JOBCODE, and SALARY columns were created in uppercase. They therefore retain this case in the new table unless you rename them. Here is partial output from the example:
DBMS Table Created with Nonstandard and Standard Column Names
Payroll Table with Revised Column Names
ID # SEX JOBCODE SALARY BirthDate HiredDate
------------------------------------------------------------------------
1118 M PT3 11379 16JAN1944:00:00:00 18DEC1980:00:00:00
1065 M ME2 35090 26JAN1944:00:00:00 07JAN1987:00:00:00
1409 M ME3 41551 19APR1950:00:00:00 22OCT1981:00:00:00
1401 M TA3 38822 13DEC1950:00:00:00 17NOV1985:00:00:00
1890 M PT2 91908 20JUL1951:00:00:00 25NOV1979:00:00:00
| Preserving Table Names |
The following example uses PROC PRINT to print the DBMS table PAYROLL. The DBMS table was created in uppercase and since PRESERVE_TAB_NAMES=YES, the table name must be specified in uppercase. (If you set the PRESERVE_TAB_NAMES=NO, you can specify the DBMS table name in lowercase.) A partial output follows the example.
options nodate linesize=64; libname mydblib oracle user=testuser password=testpass path='ora8_servr' preserve_tab_names=yes; proc print data=mydblib.PAYROLL; title 'PAYROLL Table'; run;
DBMS Table with a Case-Sensitive Name
PAYROLL Table Obs IDNUM SEX JOBCODE SALARY BIRTH 1 1919 M TA2 34376 12SEP1960:00:00:00 2 1653 F ME2 35108 15OCT1964:00:00:00 3 1400 M ME1 29769 05NOV1967:00:00:00 4 1350 F FA3 32886 31AUG1965:00:00:00 5 1401 M TA3 38822 13DEC1950:00:00:00
The following example submits a SAS/ACCESS LIBNAME statement and then opens the SAS Explorer window, which lists the Oracle tables and views that are referenced by the MYDBLIB libref. Notice that 16 members are listed and that all of the member names are in the case (initial capitalization) that is set by the Explorer window. The table names are capitalized because PRESERVE_TAB_NAMES= defaulted to NO.
libname mydblib oracle user=testuser pass=testpass;
SAS Explorer Window Listing DBMS Objects
![[SAS Explorer Window Listing DBMS Objects]](images/preserve-expl-no.gif)
If you submit a SAS/ACCESS LIBNAME statement with PRESERVE_TAB_NAMES=YES and then open the SAS Explorer window, you see a different listing of the Oracle tables and views that the MYDBLIB libref references.
libname mydblib oracle user=testuser password=testpass preserve_tab_names=yes;
SAS Explorer Window Listing Case-Sensitive DBMS Objects
![[SAS Explorer Window Listing Case-Sensitive DBMS Objects]](images/preserve-expl-yes.gif)
| Using DQUOTE=ANSI |
The following example creates a DBMS table with a blank space in its name. Double quotation marks are used to specify the table name, International Delays. Both of the preserve names LIBNAME options are also set by using the alias PRESERVE_NAMES=. Because PRESERVE_NAMES=YES, the schema airport is now case sensitive for Oracle.
options linesize=64 nodate; libname mydblib oracle user=testuser password=testpass path='airdata' schema=airport preserve_names=yes; proc sql dquote=ansi; create table mydblib."International Delays" as select int.flight as "FLIGHT NUMBER", int.dates, del.orig as ORIGIN, int.dest as DESTINATION, del.delay from mydblib.INTERNAT as int, mydblib.DELAY as del where int.dest=del.dest and int.dest='LON'; quit; proc sql dquote=ansi outobs=10; title "International Delays"; select * from mydblib."International Delays";
Notice that you use single quotation marks to specify the data value for London (int.dest='LON' ) in the WHERE clause. Because of the preserve name LIBNAME options, using double quotation marks would cause SAS to interpret this data value as a column name.
DBMS Table with Nonstandard Column Names
International Delays
FLIGHT
NUMBER DATES ORIGIN DESTINATION DELAY
-----------------------------------------------------------
219 01MAR1998:00:00:00 LGA LON 18
219 02MAR1998:00:00:00 LGA LON 18
219 03MAR1998:00:00:00 LGA LON 18
219 04MAR1998:00:00:00 LGA LON 18
219 05MAR1998:00:00:00 LGA LON 18
219 06MAR1998:00:00:00 LGA LON 18
219 07MAR1998:00:00:00 LGA LON 18
219 01MAR1998:00:00:00 LGA LON 18
219 02MAR1998:00:00:00 LGA LON 18
219 03MAR1998:00:00:00 LGA LON 18
If you query a DBMS table and use a label to change the FLIGHT NUMBER column name to a standard SAS name (Flight_Number), a label (enclosed in single quotation marks) changes the name only in the output. Because this column name and the table name, International Delays, each have a space in their names, you have to enclose the names in double quotation marks. A partial output follows the example.
options linesize=64 nodate; libname mydblib oracle user=testuser password=testpass path='airdata' schema=airport preserve_names=yes; proc sql dquote=ansi outobs=5; title "Query from International Delays"; select "FLIGHT NUMBER" label='Flight_Number', dates, delay from mydblib."International Delays";
Query Renaming a Nonstandard Column to a Standard SAS Name
Query from International Delays Flight_ Number DATES DELAY -------------------------------------- 219 01MAR1998:00:00:00 18 219 02MAR1998:00:00:00 18 219 03MAR1998:00:00:00 18 219 04MAR1998:00:00:00 18 219 05MAR1998:00:00:00 18
You can preserve special characters by specifying DQUOTE=ANSI and using double quotation marks around the SAS names in your SELECT statement.
proc sql dquote=ansi; connect to oracle (user=testuser password=testpass); create view myview as select "Amount Budgeted$", "Amount Spent$" from connection to oracle (select "Amount Budgeted$", "Amount Spent$" from mytable); quit; proc contents data=myview; run;
Output from this example would show that Amount Budgeted$ remains Amount Budgeted$ and Amount Spent$ remains Amount Spent$.
| Using Name Literals |
The following example creates a table using name literals. You must specify the SAS option VALIDVARNAME=ANY in order to use name literals. Use PROC SQL to print the new DBMS table because name literals work only with PROC SQL and the DATA step. PRESERVE_COLUMN_NAMES=YES is required only because the table is being created with nonstandard SAS column names.
options ls=64 validvarname=any nodate; libname mydblib oracle user=testuser password=testpass path='ora8servr' preserve_col_names=yes preserve_tab_names=yes ; data mydblib.'Sample Table'n; 'EmpID#'n=12345; Lname='Chen'; 'Salary in $'n=63000; proc sql; title "Sample Table"; select * from mydblib.'Sample Table'n;
DBMS Table to Test Column Names
Sample Table
Salary
EmpID# Lname in $
-------------------------
12345 Chen 63000
| Using DBMS Data to Create a DBMS Table |
The following example uses PROC SQL to create a DBMS table based on data from other DBMS tables. You preserve the case sensitivity of the aliased column names by using PRESERVE_COL_NAMES=YES. A partial output is displayed after the code.
libname mydblib oracle user=testuser password=testpass path='hrdata99' schema=personnel preserve_col_names=yes; proc sql; create table mydblib.gtforty as select lname as LAST_NAME, fname as FIRST_NAME, salary as ANNUAL_SALARY from mydblib.staff a, mydblib.payroll b where (a.idnum eq b.idnum) and (salary gt 40000) order by lname; proc print noobs; title 'Employees with Salaries over $40,000'; run;
Employees with Salaries over $40,000
ANNUAL_
LAST_NAME FIRST_NAME SALARY
BANADYGA JUSTIN 88606
BAREFOOT JOSEPH 43025
BRADY CHRISTINE 68767
BRANCACCIO JOSEPH 66517
CARTER-COHEN KAREN 40260
CASTON FRANKLIN 41690
COHEN LEE 91376
FERNANDEZ KATRINA 51081
| Using a SAS Data Set to Create a DBMS Table |
The following example uses a SAS DATA step to create a DBMS table, College-Hires-1999, from a temporary SAS data set that has case-sensitive names. It creates the temporary data set and then defines the LIBNAME statement. Because it uses a DATA step to create the DBMS table, it must specify the table name as a name literal and specify the PRESERVE_TAB_NAMES= and PRESERVE_COL_NAMES= options (in this case, by using the alias PRESERVE_NAMES=).
options validvarname=any nodate; data College_Hires_1999; input IDnum $4. +3 Lastname $11. +2 Firstname $10. +2 City $15. +2 State $2.; datalines; 3413 Schwartz Robert New Canaan CT 3523 Janssen Heike Stamford CT 3565 Gomez Luis Darien CT ; libname mydblib oracle user=testuser password=testpass path='hrdata99' schema=hrdept preserve_names=yes; data mydblib.'College-Hires-1999'n; set College_Hires_1999; proc print; title 'College Hires in 1999'; run;
DBMS Table with Case-Sensitive Table and Column Names
College Hires in 1999 Obs IDnum Lastname Firstname City State 1 3413 Schwartz Robert New Canaan CT 2 3523 Janssen Heike Stamford CT 3 3565 Gomez Luis Darien CT
|
|
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.