Chapter Contents |
Previous |
Next |
SAS/ACCESS Software for Relational Databases: Reference |
Beginning in Version 7 of SAS software, SAS variable
names and member names can be up to 32 characters long. Column names can be
case-sensitive also. Some DBMSs allow case sensitive column and table names
as well as names with special characters such as an Oracle column named
Amount Budgeted$
. Therefore,
special consideration should be used when the names of DBMS objects, such
as tables and columns, are used with SAS/ACCESS and the SQL Procedure Pass-Through
Facility. See your DBMS chapter for more information about the SAS System
and how it interfaces with your DBMS names.
When your SAS/ACCESS engine is reading column names
that do not conform to the SAS naming conventions, unsupported characters,
such as spaces, are replaced with underscores (_). This is the default behavior.
For example, the column name
Amount Budgeted$
becomes the SAS variable
name
Amount_Budgeted_
. If the DBMS name is longer than
32 characters, then the name is truncated. If necessary, numbers are applied
to the end of the name to make it unique. See The DQUOTE=ANSI PROC SQL Option to override this default renaming algorithm.
In the following example, a connection is made to an ORACLE database and a view, MYVIEW, is created from the table, MYTABLE. The output produced by PROC CONTENTS would show that the ORACLE column names, that were processed by the SQL Pass-Through view of MYTABLE, were renamed to different SAS variable names: "Amount Budgeted$" becomes "Amount_Budgeted_" and "Amount Spent$" becomes "Amount_Spent_".
proc sql; connect to oracle (user=testuser pass=testpass); create view myview as select * from connection to oracle (select "Amount Budgeted$", "Amount Spent$" from mytable); quit; proc contents data=myview; run;
See SAS Names and Support for DBMS Names for more information about SAS names and DBMS names.
The DQUOTE=ANSI PROC SQL Option |
In the following example, a connection is made to an ORACLE database. By specifying DQUOTE=ANSI and double quoting the SAS names in the SELECT statement, the special characters are preserved in the output.
proc sql dquote=ansi; connect to oracle (user=testuser pass=testpass); 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$".
You also can use the global system option VALIDVARNAME= ANY to override the SAS naming conventions. See SAS Names and Support for DBMS Names for more information.
See SAS Names and Support for DBMS Names for more information about SAS names and DBMS names.
Version 6 Names Compatibility |
options validvarname=v6; proc sql; connect to oracle (user=testuser pass=testpass); create view myview as select amount_b amount_s from connection to oracle (select "Amount Budgeted$", "Amount Spent$" from mytable); quit; options validvarname=v6; proc contents data=myview; run;
Output from this example would show that "Amount Budgeted$" becomes "AMOUNT_B" and "Amount Spent$" becomes "AMOUNT_S" .
See SAS Names and Support for DBMS Names for more information.
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.