VALIDVARNAME= V7 UPCASE ANY
VALIDVARNAME= option is generally used in SAS whenever we want to control the SAS variable names in the dataset.
VALIDVARNAME= V7 UPCASE ANY
The default option will be VALIDVARNAME=V7 until we specify as UPPERCASE or ANY.
When we mention options VALIDVARNAME=V7, that means we are telling SAS to change the name of the Database column (etc EXCEL sheet column) to valid SAS name with certain rules keeping in mind.
Here are those rules that SAS needs to follow, when it changes the DBMS column name to valid SAS name.
Only 32 mixed case (lower or uppercase) characters are allowed in each variable.
Names should be starting with an underscore or an alphabet (either uppercase or lower case character).
Invalid characters in the DBMS column (ex. $) should be changed to underscores.
See the SAS Language Reference: Dictionary to get more details about the rules.
VALIDVARNAME=UPCASE
When we mention options VALIDVARNAME=UPPERCASE we are telling SAS to change the column name of the Database column to uppercase variables irrespective of type of variables in the DBMS column.
And whenever we want the same kind of characters in SAS dataset which are in the DBMS column (ex .(=) sign and the Asterisk(*) or the forward slash(\) we have to mention options
VALIDVARNAME=ANY
If we do, this will allows any characters which are in the DBMS column to be kept in the SAS dataset.
To understand the concept better here I am giving the example:
Example
The following example shows how the Pass-Through Facility works with
VALIDVARNAME=UPPERCASE.
options validvarname=uppercase;
proc sql;
connect to oracle as tables(user=USERID orapw=passward path=’INSTANCE’);
create table lab as
select lab_rslt, lab_test
from connection to oracle
(select "laboratory result$", "laboratory test$"
from DBMStable);
quit;
When we check the Output we observe that the variables in the DBMS column is changed to upper case as well as V7 (default option) converts those variables into UPPERCASE variables. Ex: " laboratory result$" becomes LAB_RSLT and " laboratory test$" becomes LAB_TEST.
