_numeric_, _character_ and _all_ in a where dataset option

[xxformat_com]

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

[FreelanceReinh]

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.

[Rick_SAS]

You can use a subsetting IF statement instead:

data test2;
   set test;
   if sum(of _NUMERIC_)=. and clientid=cats(of _CHARACTER_);
run;

[Kurt_Bremser · Accepted solution]

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.

[yabwon]

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