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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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