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.
/* 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;
I meant as if MED1, MED2 and MED3 has the values in the list.
@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) ...
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.
How is your list of 98 codes stored? Can it be a SAS data set with 98 observations and a single variable?
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?
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?
Yes
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;
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.
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.
Ready to level-up your skills? Choose your own adventure.