Using unusual variable names using option Validvarname=any | upcase ....

Look at the SAS dataset viewed in the screenshot below…How did I name the variables in the dataset?

I guess many would say I put in a label for the variable names….right…..

Now look at this screenshot which shows the properties of the variable…

Noticed the column names…These are real column names…

One could argue that SAS Variable naming rules are as follows:-

-SAS variable names can be up to 32 characters in length.
-The first character must begin with an English letter or an underscore. Subsequent characters can be English letters, numeric digits, or underscores.
-A variable name cannot contain blanks.
-A variable name cannot contain any special characters other than the underscore.
-A variable name can contain mixed case. Mixed case is remembered and used for presentation purposes only. (SAS stores the case used in the first reference to a variable.) When SAS processes variable names, however, it internally uppercases them. You cannot, therefore, use the same letters with different combinations of lowercase and uppercase to represent different variables. For example, cat, Cat, and CAT all represent the same variable.
-You do not assign the names of special SAS automatic variables (such as _N_ and _ERROR_) or variable list names (such as _NUMERIC_, _CHARACTER_, and _ALL_) to variables.

Then How did I do that?

In SAS 8 and later, to use embedded spaces and special symbols, one need to specify the following system option…

In SAS 8 and later, to use embedded spaces and special symbols, one need to specify the following system option…

options validvarname=any;

Now look at the default system validvarname value by using proc options…

proc options option=validvarname value;run;

V7 is the dafault setting..All the above SAS naming convention is for V7 setting.

The setting of the VALIDVARNAME= system option determines what rules apply to the variables that you can create and process in your SAS session as well as to variables that you want to read from existing data sets. The VALIDVARNAME= option has three settings (V7, V6, UPCASE, and ANY), each with varying degrees of flexibility for variable names:

V6 – indicates that only those variable names that are considered valid in Version 6 are considered valid SAS variable names.

UPCASE -is the same as V7, except that variable names are uppercased, as in earlier versions of SAS.

ANY -
a)SAS variable names can be up to 32 characters in length.
b)The name can start with or contain any characters, including blanks.
c)A variable name can contain mixed case. Mixed case is stored and used for presentation purposes only. (SAS stores the case that is used in the first reference to a variable.)

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 special characters…

So finally how did I generate the dataset….Look at the syntax below…you can name the variable within ‘ ‘n or “”n.

options validvarname=any;

data test ;

'#1 @ "SASTechies"'n = 'SASTechies'; /*variable Assignment*/   "sastechies.blogspot.com's page"N = 1 ; /*variable Assignment*/

run ;

Where would one use this?

When Validvarname=V6 is specified in Pass-Through Facility code, the DBMS engine truncates column names to eight characters as it did in Version 6. If required, numbers are appended to the end of the truncated name to make it unique.

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;

Look at the SQL above….the DBMS column name “Amount Budgeted$” and “Amount Spent$” gets truncated to “amount_b” and “amount_s” becoz of the SAS v6 variable naming convention says max length for variable names is 8.
Validvarname=ANY is the option employed for reading DBMS tables column names with SAS/ACCESS…

References

-Rules for Naming SAS Variables http://support.sas.com/onlinedoc/913/getDoc/en/lrcon.hlp/a000998953.htm

SAS Monotonic() function to create observation number in Proc SQL...

In SQL, there is no concept of an observation number in tables, as there is when using Data step (i.e. _N_ or a SUM statement count+1) with a SAS dataset. In a dataset, we can select data using an observation number by using firstobs= obs= or _N_.

data class;

set sashelp.class;

count+1;

run;

Proc SQL with the NUMBER option prints the row number on a report but does not store the value in the dataset.

In SAS 9 and later, the MONOTONIC() function will return the equivalent observation number that can be stored in the dataset…

proc sql;

create table class1 as

select monotonic() as rowno, *

from sashelp.class;

quit;

You can also the monotonic() to subset the dataset in the where clause to limit the number of records read / outputted to the final dataset…

proc sql;

create table class2 as

select monotonic() as rowno, *

from sashelp.class

where 10 le monotonic() le 20;

quit;