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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.