Calculating proportion of IDs reimbursed at least once for a drug class

[Juliette2]

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;

[PeterClemmensen · Accepted solution]

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]

Thank you! This does what I need perfectly!

[PeterClemmensen]

@Juliette2 Anytime. I'm glad you found your answer 🙂