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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.