SAS Names and Support for DBMS Names Preserving Column Names 13
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
In the following example, you use the Oracle table PAYROLL to create a new Oracle
table, PAY1, and then you print 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 Oracle table name pay1 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 column name, whether you specify it 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; therefore, they retain this
case in the new table (unless you rename them). A partial output from the example is
shown:
Output 2.2 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
