Hello all,
I’m a new sas user and I’m stuck.
I have a patient dataset that records medication reimbursements. I am trying to calculate the proportion of patients that consume each drug (ie that have at least one line of reimbursement for a given drug).
Proc freq will give me overall frequencies of which drugs are most acquired but this is not what I’m looking for.
I can do this « manually » with the following code which will give me the number of patients reimbursed at least once for a drug, which I would then divide over total number of patients.
Proc sql ;
Select Count (distinct ID)
Where drug=’C1’ ;
Quit ;
However this is tedious as I have a large number of medication classes.
Any help would be greatly appreciated. Thank you !
data have;
input ID date drug;
datalines;
A 01JAN2022 C1
A 03FEB2022 C1
A 03FEB2022 C3
A 02MAR2022 C1
A 02MAR2022 C4
B 01JAN2022 C1
B 03FEB2022 C1
C 03FEB2022 C2
C 02MAR2022 C1
D 02MAR2022 C4
D 02APR2022 C4
D 02MAR2022 C1
E 02MAR2022 C4
E 02APR2022 C4
F 03FEB2022 C2
run;
Data want;
input drug percentage;
datalines;
C1 0.66
C2 0.33
C3 0.17
C4 0.5
run;
Welcome to the SAS Community 🙂
Since you already tried an SQL approach, here is one
data have;
input ID $ date :date9. drug $;
format date date9.;
datalines;
A 01JAN2022 C1
A 03FEB2022 C1
A 03FEB2022 C3
A 02MAR2022 C1
A 02MAR2022 C4
B 01JAN2022 C1
B 03FEB2022 C1
C 03FEB2022 C2
C 02MAR2022 C1
D 02MAR2022 C4
D 02APR2022 C4
D 02MAR2022 C1
E 02MAR2022 C4
E 02APR2022 C4
F 03FEB2022 C2
;
proc sql;
create table want as
select drug
, divide(count(distinct ID),
(select count(distinct ID) from have)) as percentage format = 8.2
from have
group by drug
;
quit;
Result:
drug percentage C1 0.67 C2 0.33 C3 0.17 C4 0.50
Welcome to the SAS Community 🙂
Since you already tried an SQL approach, here is one
data have;
input ID $ date :date9. drug $;
format date date9.;
datalines;
A 01JAN2022 C1
A 03FEB2022 C1
A 03FEB2022 C3
A 02MAR2022 C1
A 02MAR2022 C4
B 01JAN2022 C1
B 03FEB2022 C1
C 03FEB2022 C2
C 02MAR2022 C1
D 02MAR2022 C4
D 02APR2022 C4
D 02MAR2022 C1
E 02MAR2022 C4
E 02APR2022 C4
F 03FEB2022 C2
;
proc sql;
create table want as
select drug
, divide(count(distinct ID),
(select count(distinct ID) from have)) as percentage format = 8.2
from have
group by drug
;
quit;
Result:
drug percentage C1 0.67 C2 0.33 C3 0.17 C4 0.50
@Juliette2 Anytime. I'm glad you found your answer 🙂
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.