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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.