SAS/ACCESS Software for Relational Databases: Reference

Long Names and Case Sensitivity in the SQL Procedure and Pass-Through Facility

In Version 7 of the SAS System, 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 DBMS 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

The PROC SQL option DQUOTE=ANSI can be specified to enable support for DBMS names with special characters. When you specify DQUOTE=ANSI, table and column names in your SQL statements can be enclosed in double quotes to preserve any special characters.

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

If you have existing PROC SQL Pass-Through applications that were written using Version 6 of the SAS System, you can still run them by specifying the global system option VALIDVARNAME=V6. In Version 6, SAS variable names were uppercased and truncated to 8 characters. The following example shows how the PROC SQL Pass-Through facility works in Version 6 compatibility mode.

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