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.
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;
*Note: I just realized some other parts of my code are wrong. Please ignore them for the sake of the question posed. Thank you
Why does N_SUBJ change when there's no condition attached to it?
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.