finding frequency of a specific drug two per id

[lillymaginta]

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

[stat_sas]

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;

[PGStats]

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

[PGStats · Accepted solution]

Your request translated into SQL:

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;

[Ksharp]

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;

[lillymaginta]

Thank you all for posting suggestions!