DATA Step, Macro, Functions and more

Efficient use of Where statement

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

Efficient use of Where statement

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


Accepted Solutions
Solution
‎11-02-2015 05:20 PM
Super User
Posts: 5,083

Re: Efficient use of Where statement

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 17,837

Re: Efficient use of Where statement

Post what you've tried that generates the error.

You should be able to combine them with an AND and appropriate brackets.
Super User
Posts: 10,500

Re: Efficient use of Where statement

"Like" is an SQL function, not datastep.

 

If you DX codes are character try:

 

DX1 =: "493" or ... 

 

The =: is "starts with"

Solution
‎11-02-2015 05:20 PM
Super User
Posts: 5,083

Re: Efficient use of Where statement

[ Edited ]

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

Frequent Contributor
Posts: 121

Re: Efficient use of Where statement

This syntax worked well.  Thank you!

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 239 views
  • 2 likes
  • 4 in conversation