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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: