SAS/ACCESS Software for Relational Databases: Reference

OLE DB Naming Conventions

Because OLE DB is not a database but rather is an application programming interface (API), data source names for files, tables, and columns are determined at run time, as described here. In Version 7 and later, most SAS names can be up to 32 characters long. The SAS/ACCESS interface for OLE DB also supports file, table, and column names up to 32 characters long. If the file, table, or column names are longer than 32 characters, they will be truncated to 32 characters. For more information, see SAS names.

PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= are two SAS/ACCESS LIBNAME options that specify whether to preserve blank spaces, special characters, and mixed case in file, table, or column names. By setting these options to YES, SAS preserves the case-sensitivity of the names. If PRESERVE_TAB_NAMES=NO and PRESERVE_COL_NAMES=NO, then file, table, and column names that are read from the data source are converted to SAS names by using the SAS-name normalization rules.

This example specifies a Microsoft SQL Server provider that interacts with OLE DB. SQL Server is generally case-sensitive and, as a provider to OLE DB, it takes the default value YES for the preserve-name options. Therefore, this example would produce a SQL Server table named staffids with a column named ID Num.

options validvarname=any;

libname mydblib oledb provider=sqloledb properties=('UserID'=DIETER
        password=FRUEHAUF "data source"="HR@00123");

proc sql dquote=ansi;
create table staffids as
  select IDNUM as 'ID Num'n
   from mydblib.STAFF;
quit;

proc print data=work.staffids;
run;

If the data source were ORACLE, which is not case sensitive and which has a default value of NO for the preserve-name options, the example would produce an ORACLE table named STAFFIDS with a column named ID_NUM. The column names would be normalized to uppercase and an underscore would be substituted for blank spaces or characters that are not valid in SAS names.

For more information about the PRESERVE_COL_NAMES= and PRESERVE_TAB_NAMES= options, see SAS/ACCESS LIBNAME Options.

Chapter Contents
Previous
Next
Top of Page