topic Re: Efficient use of Where statement in SAS Programming

Efficient use of Where statement

jenim514 — Mon, 02 Nov 2015 20:19:40 GMT

Im creating a table from a larger data set, and I know there must be a more efficient way to do this. I need to select patients between the age of 2 and 13 with a diagnosis "493XX" in the DX1, DX2,DX3 or DX4 variable field. Right now, I'm doing this in 2 tables-- 1st grabing the age group I need, and then in a seperate data step, searching the DX from that table.

data need1_C1407;

set have_ C1407;

where Patage between 2 and 13;

run;

data need_AD1407;

set need1_C1407;

where dx1 like '493%' or

dx2 like '493%' or

dx3 like '493%' or

dx4 like '493;

run;

I've tried doing this in one step, but I keep getting an error. Thanks!!

Re: Efficient use of Where statement

Reeza — Mon, 02 Nov 2015 20:28:56 GMT

Post what you've tried that generates the error.

You should be able to combine them with an AND and appropriate brackets.

Re: Efficient use of Where statement

ballardw — Mon, 02 Nov 2015 21:10:24 GMT

"Like" is an SQL function, not datastep.

If you DX codes are character try:

DX1 =: "493" or ...

The =: is "starts with"

Re: Efficient use of Where statement

Astounding — Mon, 02 Nov 2015 21:15:47 GMT

In addition to Reeza's suggestion, the program would be slightly faster to switch from " like " to " =: ". In combination, the result might look like this:

data want;

set have;

where (2 <= Patage <= 13)

and (dx1 =: '493' or dx2 =: '493' or dx3 =: '493' or dx4 =: '493');

run;

(Looks like great minds think alike, and perhaps at the same time!)

Re: Efficient use of Where statement

jenim514 — Mon, 02 Nov 2015 22:20:26 GMT

This syntax worked well. Thank you!