topic Re: Proc SQL conditional count in SAS Programming

Proc SQL conditional count

wj2 — Wed, 07 Oct 2020 15:06:44 GMT

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

Re: Proc SQL conditional count

PGStats — Wed, 07 Oct 2020 15:39:47 GMT

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;

Re: Proc SQL conditional count

sqlGoddess — Tue, 28 Sep 2021 14:50:41 GMT

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