topic how can i use where condition for each step using proc sql in SAS Programming

how can i use where condition for each step using proc sql

vraj1 — Wed, 12 Jul 2017 11:23:59 GMT

I am counting number of subjects of screened and screen failure and need to condition them based on condition

proc sql;
create table scrnd as
select count(SUBJID) as screened

where term="SCREENED" from subjd;

create table scrfail as
select count(SUBJID) as scrnfail

where term="SCRNFAIL" from subjd;

quit;

Is there any precise way to do only in one create statement to get rid of set later?

Re: how can i use where condition for each step using proc sql

Kurt_Bremser — Wed, 12 Jul 2017 11:28:34 GMT

Since you get only 1-column,1-row datasets in each step, what are you planning to do with the contents of these datasets later?

Re: how can i use where condition for each step using proc sql

vraj1 — Wed, 12 Jul 2017 11:30:33 GMT

I am making a table of various counts of patients and wondering if there is any smarter way in doing in single create statement

Re: how can i use where condition for each step using proc sql

Kurt_Bremser — Wed, 12 Jul 2017 11:36:09 GMT

Use proc freq for that:

data have;
input subjid term $;
cards;
1 SCREENED
2 SCREENED
3 SCRNFAIL
4 SCREENED
5 SCRNFAIL
;
run;

proc freq data=have;
tables term / out=want;
run;

Re: how can i use where condition for each step using proc sql

RW9 — Wed, 12 Jul 2017 11:39:51 GMT

There are numerous options, as @Kurt_Bremser proc freq is the simplest. You could also use union:

proc sql;
    create table scrnd as
    select count(SUBJID) as screened  
    from subjd
    where term="SCREENED"
    union all
    select count(SUBJID) as scrnfail
    from subjd
    where term="SCRNFAIL"; 
quit;

Or use a datastep and retain counts. To be honest your question is pretty vague.