BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lillymaginta
Lapis Lazuli | Level 10
id   drug 
1    a
1   a
1   a
1  b
1  c
1 c
2 a
2 b
2 b
2 b
2 c
2 c

I have the following data. I want to create an output that provide me with the total number of id who have 2 or more of a specific drug. For example for id1: count the id one for a, none for b, and one for c

here is the output I am looking for

drug  total ids

a    1

b  1

c  2 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
lakshmi_74
Quartz | Level 8
proc sql;
select drug, count(*) from
(select id,drug,count(*) as total from have group by id,drug having calculated total ge 2)
group by drug;
quit;

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in the form of a datastep!

 

Just a rough guess:

proc sql;
  create table WANT as
  select ID,
           DRUG
           count(*) as RES
  from   HAVE
  group by ID,DRUG
  having RES >= 2;
quit;
Astounding
PROC Star

If you know how to read PROC FREQ output, two PROC FREQs should do the job nicely.

 

proc freq data=have;

tables drug * id / noprint out=counts;

run;

 

proc freq data=counts;

tables drug;

where count > 1;

run;

 

The output data set from the first PROC FREQ counts the number of observations for each DRUG / ID combination.

 

The report from the second PROC FREQ will give you a list of the drugs, and the FREQUENCY column is the number of IDs.

lakshmi_74
Quartz | Level 8
proc sql;
select drug, count(*) from
(select id,drug,count(*) as total from have group by id,drug having calculated total ge 2)
group by drug;
quit;
lillymaginta
Lapis Lazuli | Level 10

Thank you all! 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 3113 views
  • 5 likes
  • 4 in conversation