SAS/ACCESS Software for Relational Databases: Reference

Naming Examples

In this example, you create a simple table to test for yourself how the options work. To use name literals, you must specify the SAS system option VALIDVARNAME=ANY. Notice that you print the new DBMS table using PROC SQL because name literals work only with PROC SQL and the DATA step.

options ls=64 validvarname=any nodate;

libname mydblib oracle user=yao password=cary 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

DBMS column and table names that contain characters or blanks that are not valid in SAS cannot be specified directly in a SAS DATA step or procedure, except if you are using:

the DQUOTE=ANSI option in PROC SQL

SAS name literals written as
```
 'string'n
```

Therefore, you must first rename these kinds of DBMS names as standard SAS names in a PROC SQL or data set view, and then reference that view in a DATA step or SAS procedure.

In the following example, notice that the LIBNAME statement is embedded in the PROC SQL view. Output follows the example.

libname mysaslib 'SAS-data-library';

proc sql dquote=ansi;
create view mysaslib.sampleview as
  select "EmpID#" as Empid, "Salary in $" as Salary 
  from mydblib."Sample Table"      
using libname mydblib oracle user=karin 
    password=haggis path='ora8servr' 
    preserve_col_names=yes preserve_tab_names=yes;

proc print data=mysaslib.sampleview;
  title 'Sample View';
run;

PROC SQL View to Test Column Names

Sample View Obs Empid Salary 1 12345 63000

For more information about embedded libnames in PROC SQL views, see the SQL Procedure chapter in the SAS Procedures Guide.

You can then drop your sample DBMS table and PROC SQL view by using a PROC SQL DROP statement. Notice that the VALIDVARNAME=ANY option must be set in order for you to specify a name literal in the DROP statement:

options validvarname=any nodate; 
libname mysaslib 'SAS-data-library'; 
libname mydblib oracle user=yao password=cary path='ora8servr' 
preserve_tab_names=yes;

proc sql;
drop table mydblib.'Sample Table'n;
drop view mysaslib.sampleview; 
quit;

In this example, you use PROC SQL to create a new DBMS table based on data from other DBMS tables. By using PRESERVE_COL_NAMES=YES, you preserve the case-sensitivity of the aliased column names. A partial output is displayed after the code.

libname mydblib oracle user=shella password=moiri
  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;

Updating DBMS Data

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

In the next example, you create a temporary SAS data set that has case-sensitive names. You define your LIBNAME statement and then use a SAS DATA step to create the new DBMS table, College-Hires-1999. Because you are using a DATA step to create the DBMS table, you 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=YES) .

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=shella password=moiri
  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

Chapter Contents
Previous
Next
Top of Page