Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
PROC Star

## Re: Efficient use of Where statement

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

4 REPLIES 4
Super User

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

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

PROC Star

## Re: Efficient use of Where statement

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

Pyrite | Level 9

## Re: Efficient use of Where statement

This syntax worked well.  Thank you!

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