DATA Step, Macro, Functions and more

finding frequency of a specific drug two per id

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 110
Accepted Solution

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
Respected Advisor
Posts: 4,651

Re: finding frequency of a specific drug two per id

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;
PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

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;

Respected Advisor
Posts: 4,651

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
Respected Advisor
Posts: 4,651

Re: finding frequency of a specific drug two per id

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;
PG
Super User
Posts: 9,681

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

Re: finding frequency of a specific drug two per id

Thank you all for posting suggestions! 

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 241 views
  • 3 likes
  • 4 in conversation