BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xxformat_com
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
FreelanceReinh
Jade | Level 19

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
SAS Super FREQ

You can use a subsetting IF statement instead:

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

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
Amethyst | Level 16

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 4048 views
  • 3 likes
  • 5 in conversation