SAS/ACCESS Naming Examples

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_.

The following example uses the Oracle table, PAYROLL, to create a new Oracle table, PAY1, and then prints the table. You can use both PRESERVE_COL_NAMES=YES and the PROC SQL DQUOTE=ANSI options to preserve the case and nonstandard characters in the column names. You need not quote the column aliases to preserve mixed case. You need only 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 in this example is created in lowercase, Oracle stores the name in uppercase as PAY1. To store the table name as "pay1", 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. SEX, JOBCODE, and SALARY columns in the PAYROLL Oracle table were created in uppercase. They therefore retain this case in the new table unless you rename them. Here is partial output from the example.

                 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
  

This example uses PROC PRINT to print the DBMS table PAYROLL. The DBMS table was created in uppercase. Because PRESERVE_TAB_NAMES=YES, you must specify the table name in uppercase. (If you set the PRESERVE_TAB_NAMES=NO, you can specify the DBMS table name in lowercase.) 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;   

                        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     

This next example submits a SAS/ACCESS LIBNAME statement. It then opens the SAS Explorer window, which lists the Oracle tables and views that the MYDBLIB libref references. The 16 members are listed and all member names are in the case (initial capitalization) that the Explorer window sets. Table names are capitalized due to the PRESERVE_TAB_NAMES=NO default.

libname mydblib oracle user=testuser pass=testpass;

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;

The 18 members are listed, including one in lowercase and one with a name separated by a blank space. Because PRESERVE_TAB_NAMES=YES, SAS displays the tables names in the exact case in which they were created.

This example creates a DBMS table with a blank space in its name. Double quotation marks are used to specify the table name, International Delays. You can also set both of the preserve names LIBNAME options 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";

You can 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.

                      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 contain a space in their names, you must enclose the names in double quotation marks. 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 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$.

This example creates a table using name literals. To use name literals, you must specify the SAS option VALIDVARNAME=ANY. 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;

          Sample Table

                      Salary
     EmpID#  Lname      in $
   -------------------------
      12345  Chen      63000  

This example uses PROC SQL to create a DBMS table that is based on data from other DBMS tables. To preserve the case sensitivity of the aliased column names, use PRESERVE_COL_NAMES=YES. Partial output follows the example.

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 Greater Than $40,000';
run;

       Employees with Salaries Greater Than $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

This 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 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;

                     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