Proc SQL conditional count

[wj2]

Hello,

I am trying to use the COUNT statement with PROC SQL to perform 2 separate conditional counts from a dataset. I would like to perform the following two counts from my dataset: 1) All patients who have received any formulation of Medication A, and 2) All patients who have received formulations other than Oral formulations of Medication A (i.e., those who have received only oral formations would be excluded from the count; those who have received both non-oral and oral formulations would be included). I have included a sample of my data set below as well as my desired output. Any help with how to code this would be much appreciated!

Have:

Patient_ID	Medication	Formulation
A	C	Injection
A	B	Injection
A	A	Injection
B	A	Oral
B	B	Injection
C	A	Oral
C	A	Sublingual
D	A	Subcutaneous
D	F	Oral
E	A	Oral
E	B	Oral

Desired output:

Count_Medication_A_Any	Count_Medication_A_NotOral
5	3

[PGStats · Accepted solution]

proc sql;
select
	count(distinct patient_id) as a_any,
	count(distinct case when formulation ne "Oral" then patient_id else . end) as notOral
from myData
where Medication = "A";
quit;

[sqlGoddess]

I would employ boolean logic here which is an elegant approach to get counts of any combination of conditions.

if you'd like to learn more about this technique, come attend my  Ask-the-Expert Webinar on 5 Oct.

Here's one way I would craft code to get those counts you are looking for.

proc sql;
select
sum(Medication='A') as AnyA,
sum(formulation ne "Oral" and Medication='A') as AllANotOral
from doctor
quit;

the results are as follows.

AnyA	AllANotOral
6	3