DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

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

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?


Accepted Solutions
Solution
‎07-12-2017 07:47 AM
Super User
Posts: 6,946

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 6,946

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

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 115

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

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

Solution
‎07-12-2017 07:47 AM
Super User
Posts: 6,946

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,407

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

There are numerous options, as @KurtBremser 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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 89 views
  • 1 like
  • 3 in conversation