Hi,
I was expecting _numeric_, _character_ and _all_ to work everywhere but it doesn't seem to work in the where dataset option. Did I miss something?
SAS Online Doc: https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695105.htm
data test;
clientid='123'; a=' '; b=' '; c=' '; x=.; y=.; z=.; output;
clientid='124'; a=' '; b='b'; c=' '; x=.; y=.; z=.; output;
clientid='125'; a=' '; b=' '; c=' '; x=.; y=1; z=.; output;
clientid='126'; a='a'; b=' '; c=' '; x=.; y=.; z=2; output;
run;
*works
data test1;
set test;
if sum(of _numeric_)=. and clientid=cats(of _character_);
run;
*does not work;
data test2;
set test (where=(sum(of _numeric_)=. and subjid=cats(of _character_))));
run;
proc print data=test1 noobs;
run;
proc print data=test2 noobs;
run;
Regards,
Véronique
OF is a construct handled by the data step compiler only; the dataset options (and WHERE statements) are dealt with by the library engine, which is more or less SQL-like, and does not have the OF. To a WHERE= or WHERE, OF is just a variable name.
Hi Véronique (@xxformat_com),
I think the reason is actually that the OF operator is not available in WHERE statements and WHERE= dataset options (probably because these are closely related to the WHERE clause in PROC SQL), i.e., with WHERE(=) the OF operator doesn't even work with a single variable as in sum(of x). For a complete list of the available operators, see WHERE Statement Operators.
You can use a subsetting IF statement instead:
data test2;
set test;
if sum(of _NUMERIC_)=. and clientid=cats(of _CHARACTER_);
run;
OF is a construct handled by the data step compiler only; the dataset options (and WHERE statements) are dealt with by the library engine, which is more or less SQL-like, and does not have the OF. To a WHERE= or WHERE, OF is just a variable name.
The `WHERE` statement and the `WHERE=` data set option are executed "outside" of the Program Data Vector.
The condition can be only executed against explicitly listed variables from the data set (not from the PDV).
The `_numeric_` variables list requires the PDV to verify what numeric variables are available, similarly as the `a1-a10` or `x -- y` variables lists.
So, since "there is no PDV existing" when WHERE and WHERE= are executed, then the `_numeric_` cannot be used.
All the best
Bart
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.