## Efficient use of Where statement

Solved
Regular Contributor
Posts: 182

# 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;

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: 6,782

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

All Replies
Super User
Posts: 23,758

## 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: 13,574

## 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: 6,782

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

Regular Contributor
Posts: 182