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
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;
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;
@stat_sas, using a having clause eliminates drugs that were never prescribed twice (such as "b" here) from the output.
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;
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;
Thank you all for posting suggestions!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.