'Where' statement apply to specific column when creating a table with PROC SQL

[mariko5797]

I would like to do a 'where' statement within a mathematic SQL function. That is, I want to have the total number of patients in one column (N_SUBJ) and the total number of patients that meet a specific criteria to be a different column (N_DEV).

Code: I've been messing with the first line to see what will get rid of the initial error. 

proc sql;
create table CohortA as
/* Eligibility/enrollment */
select '_1Elig' as CAT, '_01AnyElig' as DEVTYPE, count(distinct PATID) as N_SUBJ, (count(distinct PATID from dv4 where DEV3DECOD in ('03', '04', '06', '99'))) as N_DEV
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B')
union 
select '_1Elig' as CAT, '_02NoIncl' as DEVTYPE, count(distinct PATID) as N_SUBJ, count(distinct PATID) where DEV3DECOD = '03' as N_DEV
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B')
select '_1Elig' as CAT, '_03Excl' as DEVTYPE, count(distinct PATID) as N_SUBJ, count(distinct PATID) where DEV3DECOD = '04' as N_DEV
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B')
union 
select '_1Elig' as CAT, '_04NoSign' as DEVTYPE, count(distinct PATID) as N_SUBJ, count(distinct PATID) where DEV3DECOD = '06' as N_DEV
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B') select '_1Elig' as CAT, '_01AnyElig' as DEVTYPE, count(distinct PATID) as N_SUBJ, count(distinct PATID) where DEV3DECOD in ('03', '04', '06', '99') 
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B')
union 
select '_1Elig' as CAT, '_05Oth' as DEVTYPE, count(distinct PATID) as N_SUBJ, count(distinct PATID) where DEV3DECOD = '99' as N_DEV
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B') select '_1Elig' as CAT, '_01AnyElig' as DEVTYPE, count(distinct PATID) as N_SUBJ, count(distinct PATID) where DEV3DECOD in ('03', '04', '06', '99') 
from dv4 where DVCAT = '01' and ARMCD in ('A', 'A_AND_B');
quit;

Table Desire:

Thank you in advance.

[mariko5797]

*Note: I just realized some other parts of my code are wrong. Please ignore them for the sake of the question posed. Thank you

[Reeza]

It looks like your code is roughly the same except for this:
DEV3DECOD

Is that case?

It also looks like you're trying to do totals and then subtotals in the same query? Have you tried some of more standard procs like MEANS or FREQ that do these out of the box? For the distinct count that may have to be by SQL or double PROC FREQ?

[ChrisNZ]

Why does N_SUBJ change when there's no condition attached to it?

[Tom · Accepted solution]

I cant' make heads or tails out of your posted code.  So let's just look at your question instead.

I would like to do a 'where' statement within a mathematic SQL function. That is, I want to have the total number of patients in one column (N_SUBJ) and the total number of patients that meet a specific criteria to be a different column (N_DEV).

So get a count in SQL use the COUNT() aggregate function.  It will ignore (count as zero) missing values.  So just use a CASE clause that returns a missing value for the observation you don't want to count.

proc sql;
select count(*) as n_students
     , count(case when sex='F' then 1 else . end) as n_female
     , count(case when sex='M' then 1 else . end) as n_male
from sashelp.class
;
quit;

In SAS you can also use the fact that boolean expressions evaluate as 1 for TRUE and 0 for FALSE.

proc sql;
select count(*) as n_students
     , sum(sex='F') as n_female
     , sum(sex='M') as n_male
from sashelp.class
;
quit;