SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Easy: Where Clause or anything else that would work;

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 109
Accepted Solution

Easy: Where Clause or anything else that would work;

I am looking to pull all observations that have a particular numeric value in at least one of the listed variables. Though I do not want to have to write all of the variables out. I have tried dx1-50 and also a macro to no success. See following for examples:

proc print data=test;

      where dx1-dx50 = 808.44;

      var sex;

run;

or

%let vars = dx1-50;

proc print data=test;

      where  &vars = 808.44;

      var sex;

run;

quit;

Perhaps it needs an "or"?  I am still messing around with options but may need some help. I also believe this could be done in proc sql, but I am not experienced with that procedure.


Accepted Solutions
Solution
‎07-16-2014 08:34 AM
Super User
Posts: 10,035

Re: Easy: Where Clause or anything else that would work;

stat@sas , you can do it simpler by using IN operator.

data test;

set have;

array vars{*} dx1-dx50;

if 808.44 in vars ;

run;



Xia Keshan

View solution in original post


All Replies
Super User
Posts: 19,822

Re: Easy: Where Clause or anything else that would work;

Look at the whichn function for numbers and whichc for characters.

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition.

Trusted Advisor
Posts: 1,228

Re: Easy: Where Clause or anything else that would work;

Hi,

Just create a variable check that will be increased if any of the variables defined in the array has value 808.44. Then select observations based on check variable.

data test;
set have;
check=0;
array vars{*} dx1-dx50;
do i=1 to dim(vars);
if vars{i}=808.44 then check+1;
end;
run;

proc print data=test;
where check>0;
run;

Solution
‎07-16-2014 08:34 AM
Super User
Posts: 10,035

Re: Easy: Where Clause or anything else that would work;

stat@sas , you can do it simpler by using IN operator.

data test;

set have;

array vars{*} dx1-dx50;

if 808.44 in vars ;

run;



Xia Keshan

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Easy: Where Clause or anything else that would work;

Great options. Yesterday I ended up using the option that was removed from this thread. Though, Ksharp's seems to be very concise.

Follow-up question,  Is there a good way to do this for pulling observations based on the first three digits (going left to right) of the value.  So I would like to do that exact samething but for numbers starting 958, which are actually list in the sample as 958.20, 958.3, 958.30, 958.92, etc.).

Super User
Super User
Posts: 7,974

Re: Easy: Where Clause or anything else that would work;

Well, for one: if int(number) = 958 then ... is fine.  Not sure how this would work with KSharps solution, but stats@sas 's one should be simple implementation.

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Easy: Where Clause or anything else that would work;

Ignore my last question I did it using:

array incl[60] DX1 - DX60;

                  do i = 1 to 60;

            if incl >= 958.00 and incl < 959.00 then uncoded = 1;

            end;

            drop i;

Thanks for everybody's help!!!!!!!!!

Super User
Super User
Posts: 7,060

Re: Easy: Where Clause or anything else that would work;

Typically it is better to store diagnosis codes as character instead of numbers. They are not numbers and you might get burned by codes with leading or trailing zeros or issues with exact representation of decimal numbers in binary floating point.

Most large data providers will supply ICD9 codes as a five character code with the period removed. For Example the following list you provide above (958.20, 958.3, 958.30, 958.92,) would be stored as ( '95820' '9583 '  '95830' '95892' ) and you could actually distinguish between the second and third values.

You can use the : modifier on the IN operator to effect matching on just the beginning of the string.

data test ;

  length dx1-dx5 $5. ;

  input dx1-dx5 ;

  array d dx:;

  found= '802' in d;

  found2 = '80' in: d;

  put (_all_) (=);

cards;

101 802 . . .

80  . . . .

run;

Frequent Contributor
Frequent Contributor
Posts: 109

Re: Easy: Where Clause or anything else that would work;

Thanks for the replies Tom. I was using these diagnoses in a crosswalk, which required me to do all kinds of formatting to them. So in this particular situation I had to end-up with numer values, but I know what you mean about all of the possibilities to get "burned" by them .

Super User
Super User
Posts: 7,060

Re: Easy: Where Clause or anything else that would work;

Note that the WHERE statement (or a WHERE= dataset option) is more limited than normal SAS statements.  For example you cannot use variable lists in the WHERE clause.

So while you could do:

data want ;

set test ;

if whichn(808.44, of &vars);

run;

You get a syntax error if you try the same thing with a WHERE statement instead.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 509 views
  • 5 likes
  • 6 in conversation