DATA Step, Macro, Functions and more

count ids with two or more observation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

count ids with two or more observation

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 

 

 


Accepted Solutions
Solution
‎04-10-2017 12:34 PM
Contributor
Posts: 57

Re: count ids with two or more observation

Posted in reply to lillymaginta
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


All Replies
Super User
Super User
Posts: 7,988

Re: count ids with two or more observation

Posted in reply to lillymaginta

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;
Super User
Posts: 5,516

Re: count ids with two or more observation

Posted in reply to lillymaginta

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.

Solution
‎04-10-2017 12:34 PM
Contributor
Posts: 57

Re: count ids with two or more observation

Posted in reply to lillymaginta
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;
Frequent Contributor
Posts: 128

Re: count ids with two or more observation

Posted in reply to lakshmi_74

Thank you all! 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 158 views
  • 5 likes
  • 4 in conversation