This example creates the view, Myview, from the Oracle table, Mytable.
proc sql;
connect to oracle (user=myusr1 password=mypwd1);
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 that PROC CONTENTS produces, Oracle column names that the SQL View of MYTABLE processed are renamed to different SAS variable names: Amount Budgeted$ becomes Amount_Budgeted_ and Amount Spent$ becomes Amount_Spent_.
In this example, the Oracle table, PAYROLL, creates a new Oracle table, PAY1, which it then prints. 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 do not need to 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 specify the case of table and column names. So, 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", specify PRESERVE_TAB_NAMES=NO.
options linesize=120 pagesize=60 nodate;
libname mydblib oracle user=myusr1 password=mypwd1 path='mysrv1'
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, regardless of how 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. So they 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 specify 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=myusr1 password=mypwd1
path='mysrv1' 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 example creates a DBMS table with a blank space in its name. It uses double quotation marks to specify the table name, International Delays. You can also specify 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=myusr1 password=mypwd1 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
Using a label to change the name of a DBMS column name changes only the output. Enclose the label in single quotation marks. 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=myusr1 password=mypwd1 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=myusr1 password=mypwd1);
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_COL_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=myusr1 password=mypwd1 path='mysrv1'
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=myusr1 password=mypwd1
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 specifies 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, it performs these actions 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=myusr1 password=mypwd1
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