Using Name Literals in SAS

Most of us use date or datetime literals in SAS all the time. For example, take ’01JAN2014’d — the single quotes enclosing the date constant are tagged with a “d” to announce to SAS that the enclosed value is a date, not a character string. There are also rare occasions when you may be forced to use a name literal. For instance, you may need to reference columns which have nonstandard names that include spaces and/or special characters, violating SAS naming conventions. Frequently, this happens if you are using an Excel file which has been imported into SAS (especially using the Enterprise Guide import wizard) without someone first checking to make sure that the column names are appropriate. Ideally, you would rename the columns and/or reimport the file after it has been cleaned up.

This is not always possible if you are referencing a DBMS table with nonstandard column names. If you are accustomed to writing SQL in a DBMS, you may know that you can reference column names which have spaces in them by enclosing them in brackets: [Total Cost]. This does not work in SAS, however.

The only way to do this is SAS is to use name literals to reference those columns, combined with resetting the VALIDVARNAME SAS system option as follows:

options validvarname=any;
SELECT *
FROM Table
WHERE ‘Total Cost’n > 0;

This system option by default is set to validvarname=V7 in Base SAS, but using validvarname=any causes SAS to accept nonstandard column/variable names. The import wizard in EG will “helpfully” reset the system option for you to “any”, resulting in importing the spreadsheet with the original nonstandard column names. If you then try to reference these unusual column names without using a name literal, you will get errors in your log. Here are some other examples of name literals:

‘# of Months’n
‘Date*’n
‘$ Total’n