topic Re: Easy: Where Clause or anything else that would work; in SAS Data Management

Easy: Where Clause or anything else that would work;

Tue, 15 Jul 2014 17:11:53 GMT

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;

%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.

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

Reeza — Tue, 15 Jul 2014 17:39:19 GMT

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

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

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

stat_sas — Tue, 15 Jul 2014 17:39:41 GMT

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;

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

Ksharp — Wed, 16 Jul 2014 12:34:37 GMT

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

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

Wed, 16 Jul 2014 13:44:22 GMT

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.).

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

RW9 — Wed, 16 Jul 2014 13:54:39 GMT

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.

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

Wed, 16 Jul 2014 13:59:52 GMT

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!!!!!!!!!

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

Tom — Wed, 16 Jul 2014 14:38:29 GMT

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.

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

Tom — Wed, 16 Jul 2014 14:47:39 GMT

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;

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

Wed, 16 Jul 2014 16:02:52 GMT

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 .