BookmarkSubscribeRSS Feed
Viveme789
Fluorite | Level 6

Suppose I have a dataset looks like the following:

 

ID .     age  MED1    MED2    MED3
001 .     1      1055        7832    3435
202 .      0     1051        3423    3455
204 .      3      5040         3423 .  3444
213 .       4     9999        3432 .  4546

 

Each patient has three prescriptions and each has a code comes with it. I want to see if patients age less than 5 have prescription MED1-MED3 in code (7832, 3435, 1051). 

I used proc sql the following but it gave me the same dataset. 

PROC SQL;
	CREATE TABLE WANT AS
		SELECT *
		FROM MEDS
                WHERE MED1 OR MED2 OR MED3 IN (7832, 3455, 1051) AND
AGE <= 5;
QUIT;

How can I correct this? Thanks. 

9 REPLIES 9
novinosrin
Tourmaline | Level 20

/* if i understand you correctly*/
PROC
SQL; CREATE TABLE WANT AS SELECT * FROM MEDS WHERE MED1=7832 and MED2=3455 and MED3=1051 AND AGE <= 5; QUIT;

 

Viveme789
Fluorite | Level 6

I meant as if MED1, MED2 and MED3 has the values in the list. 

Reeza
Super User

@Viveme789 wrote:

Suppose I have a dataset looks like the following:

 

ID .     age  MED1    MED2    MED3
001 .     1      1055        7832    3435
202 .      0     1051        3423    3455
204 .      3      5040         3423 .  3444
213 .       4     9999        3432 .  4546

 

Each patient has three prescriptions and each has a code comes with it. I want to see if patients age less than 5 have prescription MED1-MED3 in code (7832, 3435, 1051). 

I used proc sql the following but it gave me the same dataset. 

PROC SQL;
	CREATE TABLE WANT AS
		SELECT *
		FROM MEDS
                WHERE MED1 OR MED2 OR MED3 IN (7832, 3455, 1051) AND
AGE <= 5;
QUIT;

How can I correct this? Thanks. 


 

SQL doesn't do variable lists or multiple variables well, it would be easier in a data step, otherwise you need to duplicate your condition:

 

WHERE MED1  IN (7832, 3455, 1051) 
OR MED2  IN (7832, 3455, 1051)
OR MED3 IN (7832, 3455, 1051) ...
Viveme789
Fluorite | Level 6

Can you tell me how to do it in a data step, where I don't have to duplicate the list of values? The problem I have on hand actually have 98 codes in the list and 9 variables.... Thanks. 

Astounding
PROC Star

How is your list of 98 codes stored?  Can it be a SAS data set with 98 observations and a single variable?

Viveme789
Fluorite | Level 6

I meant that 9 variables MED1 to MED9 has to have values that contains in the list, which has 98 codes as a criteria to subset the data. For example:

 MED1 OR MED2 OR...OR MED9 IN (1, 2, ....98). Do I make sense?

Astounding
PROC Star

100%.

 

But my question remains the same.  Can you put the list of 98 possible values into a SAS data set that contains 98 observations and 1 variable?

Astounding
PROC Star

OK, assuming you have a separate SAS data set with 98 observations, and just one variable (MED):

 

data temp;

set _98_meds;

start=med;

end='In the List';

fmtname='_my98_';

run;

proc format cntlin=temp;

run;

 

This much gives you a format that translates any of the 98 meds into "In the List".  Then use that format to compare to the 9 meds (MED1 through MED9):

 

data want;

set have;

where age <= 5;

array med {9};

do _n_=1 to 9 until (flag='In the List');

   flag = put(med{_n_}, _my98_.);

end;

if flag='In the List';

drop flag;

run;

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 2645 views
  • 0 likes
  • 4 in conversation