## finding frequency of a specific drug two per id

Solved
Frequent Contributor
Posts: 136

# finding frequency of a specific drug two per id

Hi ALl,

I have the following database

``````id  drug_date drug
1   1/1/2005   a
1   1/1/2006  a
1   2/2/2007  b
1   3/3/2008  c
1  3/4/2008   c
1  1/1/2004  a
1  2/2/2005   a
2  5/5/2009   a
2  6/6/2009  a
3  7/7/2007  b

I want the frequency of drugs per id, where each id is required to have the drug dispensed two times. For example id 1: had 4 dates for drug a. Count this as 1 since I need to dates for a per id.
for id 1, don't count b because the id has only one date for b.
For id 1, count c 1 since the id has two dates for c and so on.
My output will be:
a 2
b 0
c 1``````

Accepted Solutions
Solution
‎05-02-2016 11:21 AM
Posts: 5,521

## Re: finding frequency of a specific drug two per id

``````proc sql;
create table want as
select
drug,
sum(rep>=2) as nbId
from
(   select drug, id, count(*) as rep
from have group by drug, id       )
group by drug;
select * from want;
quit;``````
PG

All Replies
Posts: 1,270

## Re: finding frequency of a specific drug two per id

proc sql;
select drug,sum(count) as count from (
select drug,count(distinct drug) as count from have
group by id,drug
having count(drug)>1)
group by drug;
quit;

Posts: 5,521

## Re: finding frequency of a specific drug two per id

@stat_sas, using a having clause eliminates drugs that were never prescribed twice (such as "b" here) from the output.

PG
Solution
‎05-02-2016 11:21 AM
Posts: 5,521

## Re: finding frequency of a specific drug two per id

``````proc sql;
create table want as
select
drug,
sum(rep>=2) as nbId
from
(   select drug, id, count(*) as rep
from have group by drug, id       )
group by drug;
select * from want;
quit;``````
PG
Super User
Posts: 10,766

## Re: finding frequency of a specific drug two per id

I am not sure I understand your requirement completely .
```data have;
input id  drug_date : \$20. drug \$;
cards;
1   1/1/2005   a
1   1/1/2006  a
1   2/2/2007  b
1   3/3/2008  c
1  3/4/2008   c
1  1/1/2004  a
1  2/2/2005   a
2  5/5/2009   a
2  6/6/2009  a
3  7/7/2007  b
;
run;
proc summary data=have;
by id drug notsorted;
output out=temp;
run;
data temp;
set temp;
count=ifn(_freq_=1,0,1);
run;
proc summary data=temp nway;
class id drug ;
var count;
output out=want(drop=_:) sum=;
run;

```
Frequent Contributor
Posts: 136

## Re: finding frequency of a specific drug two per id

Thank you all for posting suggestions!

🔒 This topic is solved and locked.