Chapter Contents


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; 
  'Salary in $'n=63000;

proc sql;
title "Sample Table";  
select * from mydblib.'Sample Table'n;

DBMS Table to Test Column Names
          Sample Table        
     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:


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

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' 

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

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

Updating DBMS Data
       Employees with Salaries over $40,000    

   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.; 
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
data mydblib.'College-Hires-1999'n;
  set College_Hires_1999;

proc print; 
title 'College Hires in 1999';

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



Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.