You cannot use %upcase(name) since that will just uppercase the variable's name and not its value.
You need the upcase(name) since the metadata will store the variable name in mixed case.
In the past I have seen it make a query that takes just fractions of a second when the WHERE clause causes SAS to skip opening datasets and libraries go to taking 20,30 or more seconds. The time hit will happen if you have librefs that point to locations with many datasets or worse that are pointing to external databases. If the WHERE clause can eliminate those without SAS having to open every dataset or query every remote database it can save a lot of time. And if some of you "tables" are actually views then the views might even need to be run.
It does look like with SAS 9.4 that SAS can now understand that upcase('work') is just a constant and so that is why you see no effect. I suspect that SAS is optimizing the query to eliminate the UPCASE() function. I am not sure when this improvement was made.
Note that if you use a DATA step to access SASHELP.VCOLUMN view instead of using PROC SQL to access either SASHELP.VCOLUMN or DICTIONARY.COLUMNS then SAS will not be able to use the WHERE condition to skip opening the datasets and libraries. The WHERE clause is not passed through the view to the query against DICTIONARY.COLUMNS, but instead is applied to the results pulled from DICTIONARY.COLUMNS.
Example:
110 proc sql noprint;
111 select count(distinct memname),count(*)
112 into :nds trimmed, :nvars trimmed
113 from dictionary.columns
114 where libname=upcase('WORK')
115 ;
116 %put &=nds &=nvars;
NDS=1 NVARS=8
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
117 proc sql noprint;
118 select count(distinct memname),count(*)
119 into :nds trimmed, :nvars trimmed
120 from sashelp.vcolumn /* dictionary.columns */
121 where libname='WORK'
122 ;
123 %put &=nds &=nvars;
NDS=1 NVARS=8
124 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.11 seconds
cpu time 0.01 seconds
125
126 data _null_;
127 set sashelp.vcolumn;
128 where libname='WORK';
129 run;
ERROR: Teradata connection: The UserId, Password or Account is invalid.
INFO: Data file SASUSER.TERADATA.DATA is in a format that is native to another host, or the file
encoding does not match the session encoding. Cross Environment Data Access will be used, which
might require additional CPU resources and might reduce performance.
NOTE: There were 8 observations read from the data set SASHELP.VCOLUMN.
WHERE libname='WORK';
NOTE: DATA statement used (Total process time):
real time 4.41 seconds
cpu time 1.62 seconds
... View more
