PROC SQL: Multiple where statements

[wj2]

Hello Community, 

I am trying to identify the number of patients in a dataset according to a set of conditions. First, I would like to identify the number of distinct patients who have received any prescription of a medication (medicationA) with a prescription start date prior to 9/1/2020 (start_date<'01SEP2020'd). This is specified via the variable "any" below. I would also like to identify the number of distinct patients who have received a non-injection formulation of medicationA with a prescription start date prior to 9/1/2020. This is specified via the variable "oral_med" in my code below. I am currently using the proc sql code below with multiple where conditions; however, this code is returning zero cases for both 'any' and 'oral_med', which is not true. Any suggestions on how to modify this code so that the conditions above can be properly obtained would be much appreciated!

proc sql;
create table want as
select
	count(distinct patientID) as any,
	count(distinct case when route_medication not in ('Injection')
then patientID else . end) as oral_med
from have
where generic_name like 'medicationA' and start_date<'01SEP2020'd;
quit;

[FreelanceReinh]

Hello @wj2,

Your code works on the test dataset HAVE created below

data have;
do patientID=1 to 8;
  do start_date='31AUG2020'd,'01SEP2020'd;
    do generic_name='medicationA','medicationB';
      do route_medication='Injection','Other';
        do _n_=1 to 3*ranuni(3);
          output;
        end;
      end;
    end;
  end;
end;
format start_date date9.;
run;

and the resulting WANT dataset

Obs    any    oral_med

 1      7         4

confirms the results of these PROC FREQ steps:

proc freq data=have;
where generic_name='medicationA' & .z<start_date<'01SEP2020'd;
tables patientID;
run;

proc freq data=have;
where generic_name='medicationA' & .z<start_date<'01SEP2020'd & route_medication ne 'Injection';
tables patientID;
run;

Therefore I suspect that the WHERE condition is not suitable for your data (or vice versa). Possible reasons include case sensitivity, other spelling differences, invisible characters in the character variables, non-SAS date values (e.g. 1092020), etc. I would scrutinize one of the observations which should be selected, but aren't.