## count ids with two or more observation

Solved
Frequent Contributor
Posts: 136

# 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

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;

All Replies
Super User
Posts: 9,838

## Re: count ids with two or more observation

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: 6,931

## Re: count ids with two or more observation

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

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: 136