topic Re: How do I get a count of unique Patient IDs grouped by two variables (DrugName & Year)? in SAS Procedures

How do I get a count of unique Patient IDs grouped by two variables (DrugName & Year)?

cbatzi01 — Thu, 16 Jun 2016 15:11:27 GMT

Hi,

I have a data set that contains Patient IDs, Drugs, and date of the prescription fill. I am trying to identify the number of individual patients that were on a drug in a given year. A patients typically have multiple records within a year, as each record representation a prescription fill date. The data structure looks like this:

Patid	DrugName	Year
100	Drug 1	2013
200	Drug 1	2013
200	Drug 1	2013
200	Drug 1	2013
200	Drug 1	2013
300	Drug 1	2013
100	Drug 1	2014
300	Drug 2	2013
100	Drug 2	2015
400	Drug 2	2015
400	Drug 2	2015
400	Drug 2	2015
300	Drug 3	2014
300	Drug 3	2015
100	Drug 3	2015

In my head, the output should should look something like this:

Drug	year	#Patients
Drug 1	2013	3
Drug 1	2014	1
Drug 2	2013	1
Drug 2	2015	1
Drug 3	2014	1
Drug 3	2015	2

I have tried proc summary, but that just gives me a frequency of the number of records, by drug & year. I am thinking proc sql using count/distinct would work, but not sure how to group by drugname & year, as I am not very good at this...

Any assistance is greatly appreciated.

Thanks!
Chris

Re: How do I get a count of unique Patient IDs grouped by two variables (DrugName & Year)?

Reeza — Thu, 16 Jun 2016 15:51:40 GMT

You can do that but I'd consider a different method because you are likely to want multiple stats.

data have2;
set have;

by id drug;
First_Drug=0;
if first.drug then First_Drug=1;

run;

proc means data=have2 sum;
class year drug;
var first_drug;
output out=want sum(first_drug) = num_patients;
run;

Re: How do I get a count of unique Patient IDs grouped by two variables (DrugName & Year)?

Astounding — Thu, 16 Jun 2016 16:10:57 GMT

PROC FREQ is pretty good at counting:

proc freq data=have;

tables DrugName*PatID*Year / noprint out=summarized;

run;

proc freq data=summarized;

tables DrugName*Year / list;

run;

The frequency column in the final PROC FREQ will be the patient count. Also note, the second PROC FREQ could create an output data set if needed.

Re: How do I get a count of unique Patient IDs grouped by two variables (DrugName & Year)?

cbatzi01 — Fri, 17 Jun 2016 17:49:03 GMT

Thanks. This worked well, and matches up with another step in my analysis.

-Chris