|SAS/ACCESS Software for Relational Databases: Reference|
Beginning in Version 7 of SAS software, SAS naming conventions have been enhanced to allow longer names for SAS data sets and SAS variables. The conventions also allow case-sensitive or mixed case names for SAS data sets and variables.
The following SAS language elements can now be up to 32 characters in length:
The following SAS language elements remain unchanged with a maximum length of 8 characters:
The following libref.dataset shows the
longer name for the data set MYDB.TEMP_EMPLOYEES_QTR4_2000. Likewise, a variable
name can be longer and defined in mixed case, such as
When you specify mixed case or case-sensitive names
in SAS code, SAS displays the names as you have specified them. In this example,
the SAS variables,
dates, are defined
in mixed case:
options nodate linesize=64; data test; input Flight $3. +3 dates date9.; format dates date9.; datalines; 114 01MAR2000 202 01MAR2000 204 01MAR2000 ; proc print data=test (keep=FLIGHT DATES); run;
Mixed Case Names Displayed in Output
SAS System Obs Flight dates 1 114 01MAR2000 2 202 01MAR2000 3 204 01MAR2000
When the TEST data set is output, the variable names are stored as they are defined, instead of automatically being stored in uppercase. However, when SAS processes the names, it can process them as FLIGHT and DATES.
Note: Because of the way
that SAS processes names, it recognizes variable names regardless of the case
in which they were created. For example, if you were to use PROC DATASETS
to rename the
the procedure would recognize
Flight even if you input it as
However, the new variable name is stored as the mixed-case name
proc datasets library=work memtype=data; modify test; rename flight=All_flights; run;
SAS Log Showing a Renamed Variable
20 proc datasets library=work memtype=data; -----Directory----- Libref: WORK Engine: V8 Physical Name: /tmp/SAS_xxxxxxxxabc File Name: /tmp/SAS_xxxxxxxxefg Inode Number: 84111 Access Permission: rwxr-xr-x Owner Name: marie File Size (bytes): 1024 File # Name Memtype size Last modified --------------------------------------------- 1 TEST DATA 16384 11MAY1999:18:38:31 21 modify test; 22 rename flight=All_flights; NOTE: Renaming variable flight to All_flights. 23 run;
|SAS Name Literals|
A SAS name
literal is a name token that is expressed as a quoted string, followed
by the letter
n. Name literals
enable you to use special characters or blanks that are not otherwise allowed
in SAS names when you specify a SAS data set or variable. Name literals are
especially useful for expressing database column and tables names that contain
Name literals are subject to certain restrictions:
Examples of name literals are
data mydblib.'My Staff Table'n; ... run;
input '$ Amount Budgeted'n 'Amount Spent'n ...run;
|SAS/ACCESS LIBNAME and PROC SQL Options|
Using the SAS/ACCESS LIBNAME statement and PROC SQL options, SAS software can handle table and column names in DBMSs that are case-sensitive or non-standard for SAS. Non-standard names includes those with blank spaces or special characters (such as @, #, %) that are not allowed in SAS names. The following list briefly describes these options. See your DBMS chapter for information about how SAS processes your DBMS-specific names.
Specify the alias PRESERVE_NAMES=YES | NO, if you plan to specify both the PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= options in your LIBNAME statement. Using this alias saves you some time when coding.
You can use the DATA step to read data from multiple data sets, in this example, two DBMS tables. This example merges data from the two DBMS tables, STAFF and SUPERV, and writes it to the SAS data set WORK.COMBINED.
options linesize=120 nodate; libname mydblib oracle user=karin password=haggis path='airhrdata' schema=airport preserve_col_names=yes; data combined; merge mydblib.staff mydblib.superv(in=super rename=(supid=idnum)); by idnum; if super; run; proc print data=combined (outobs=10); title "Supervisor Information"; run;
Note: The PRESERVE_COL_NAMES=YES LIBNAME option retains the case of
the column names from the DBMS-in this example, uppercase--when
creating the corresponding SAS variable names.
Partial output for this example is shown:
Reading Data from Multiple DBMS Tables
Supervisor Information OBS IDNUM LNAME FNAME CITY STATE HPHONE JOBCAT 1 1106 MARSHBURN JASPER STAMFORD CT 203/781-1457 PT 2 1118 DENNIS ROGER NEW YORK NY 718/383-1122 PT 3 1126 KIMANI ANNE NEW YORK NY 212/586-1229 TA 4 1352 RIVERS SIMON NEW YORK NY 718/383-3345 NA 5 1385 RAYNOR MILTON BRIDGEPORT CT 203/675-2846 ME 6 1401 ALVAREZ CARLOS PATERSON NJ 201/732-8787 TA 7 1405 DACKO JASON PATERSON NJ 201/732-2323 SC 8 1417 NEWKIRK WILLIAM PATERSON NJ 201/732-6611 NA 9 1420 ROUSE JEREMY PATERSON NJ 201/732-9834 ME 10 1431 YOUNG DEBORAH STAMFORD CT 203/781-2987 FA
In the following example, you use the ORACLE PAYROLL table to create a new ORACLE table, PAY1, and then print it. Both the PRESERVE_COL_NAMES=YES and the PROC SQL DQUOTE=ANSI options are used to preserve the case and non-standard characters in the column names. Notice that you do not need to quote the column aliases in order to preserve the mixed case. You only need double quotes when the column name has non-standard characters or blanks.
By default, the SAS/ACCESS engine for ORACLE uses the database's rules
for setting the case of table and column names. Therefore, even though the
new ORACLE table name,
is created in lowercase in this example, ORACLE stores the name in uppercase
PAY1. How table and
column names are stored is DBMS-specific; see your DBMS chapter or DBMS documentation
for more information.
options linesize=120 pagesize=60 nodate; libname mydblib oracle user=yao password=cary 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;
Recall from the description of how SAS processes columns, that SAS recognizes a column
name, regardless of how it was created. Therefore, in this example, SAS recognizes
jobcode column name,
whether you specify it in your SAS code as lowercase, mixed case, or uppercase.
In the ORACLE PAYROLL table, the SEX, JOBCODE, and SALARY columns were created
in uppercase, and therefore, they retain this case in the new table, PAY1,
unless you rename them.
A partial output from the example is shown:
DBMS Table Created with Non-Standard 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 1777 M PT3 9630 23SEP1951:00:00:00 21JUN1981:00:00:00 1404 M PT2 91376 24FEB1953:00:00:00 01JAN1980:00:00:00
In the following example, you use PROC PRINT to print the DBMS table, PAYROLL. Because the DBMS table was created in uppercase and you set the PRESERVE_TAB_NAMES=YES option, you must specify the table name in uppercase in your code. A partial output follows the example.
options nodate linesize=64; libname mydblib oracle user=yao password=cary path='ora8_servr' preserve_tab_names=yes; proc print data=mydblib.PAYROLL; title 'PAYROLL Table'; run;
DBMS Table with a Case-Sensitive Name
PAYROLL Table Obs IDNUM SEX JOBCODE SALARY BIRTH HIRED 1 1919 M TA2 34376 12SEP1960:00:00:00 04JUN1987:00:00:00 2 1653 F ME2 35108 15OCT1964:00:00:00 09AUG1990:00:00:00 3 1400 M ME1 29769 05NOV1967:00:00:00 16OCT1990:00:00:00 4 1350 F FA3 32886 31AUG1965:00:00:00 29JUL1990:00:00:00 5 1401 M TA3 38822 13DEC1950:00:00:00 17NOV1985:00:00:00
If you had omitted the PRESERVE_TAB_NAMES= option or set it to NO in this example, you could have specified the DBMS table name in lowercase.
In the next example, you create a PROC SQL view based on a DBMS table
that you created in the previous example. Because you set PRESERVE_TAB_NAMES=YES in the following
example, the name of the
PAY1 table is case-sensitive. When you also use the PRESERVE_COL_NAMES=YES
option, you can rename the columns as well.
options nodate linesize=64; libname mydblib oracle user=yao password=cary path='ora8_servr' preserve_tab_names=yes preserve_col_names=yes; proc sql dquote=ansi outobs=5; create view work.jobcodes as select "ID #" as EmpID, sex, salary from mydblib.PAY1 where Jobcode in ('TA2','TA3'); proc print data=work.jobcodes; title 'By Jobcode TA2 or TA3'; run;To simplify your coding, you could have also used the alias PRESERVE_NAMES=YES instead of listing both of the options on the LIBNAME statement.
A partial output for the example is shown:
PROC SQL View Created from a Case-Sensitive DBMS Table Name
By Jobcode TA2 or TA3 Emp Obs ID SEX SALARY 1 1401 M 38822 2 1639 F 40260 3 1480 F 39583 4 1017 M 40858 5 1876 M 39675
The SAS Explorer window has replaced the Access window in Version 7
and later. In the next example, you submit the SAS/ACCESS LIBNAME statement
with the PRESERVE_TAB_NAMES=NO option and then open the SAS Explorer window.
The resulting window lists the ORACLE tables and views that are referenced
Notice that 16 members are listed and that all of the member names are in
the case (initial capitalization) that is set by the Explorer window.
libname mydblib oracle user=jyoti pass=tiger preserve_tab_names=no;
SAS Explorer Window Listing DBMS Tables and Views
In the next
example, you submit the SAS/ACCESS LIBNAME statement with
the PRESERVE_TAB_NAMES=YES and then open the SAS Explorer window. This time,
you see a different listing of the ORACLE tables and views referenced by the
Mydblib libref. Notice that there
are 18 members listed, including one that is in lowercase and one that has
a name separated by a blank space. Because of the LIBNAME option, SAS displays
the tables names in the exact case in which they were created.
libname mydblib oracle user=jyoti pass=tiger preserve_tab_names=yes;
SAS Explorer Window Listing Case-Sensitive DBMS Tables and Views
In the next example, you create a DBMS table that is specified in double
quotes and has a blank in its name,
International Delays. Both of the preserve-names LIBNAME options are
specified using the options' alias, PRESERVE_NAMES=YES.
options linesize=64 nodate; libname mydblib oracle user=orjan pass=mypw 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";
Notice that you use single-quotes to specify the data value for London
int.dest='LON') in the
WHERE clause. Because of the preserve-name LIBNAME options, using double-quotes
would cause SAS to interpret this data value as a column name.
DBMS Table with Non-Standard Column Names
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
See SQL Procedure's Interaction with SAS/ACCESS Software and the SQL Procedure chapter in the SAS Procedures Guide for more information about the DQUOTE= option.
In the next example, you query the DBMS table and use a label to change
FLIGHT NUMBER column
name to a standard SAS name,
Flight_Number. A label--enclosed in single quotes--changes
the name only in the output. Because this column name and the table name (
International Delays) each have
a space in their names, you have to enclose the names in double-quotes. A
partial output follows the example.
options linesize=64 nodate; libname mydblib oracle user=orjan pass=mypw 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 Renaming a Non-Standard Column to a Standard SAS Name
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
See the SAS naming conventions and VALIDVARNAME for more information.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.