DATA Step, Macro, Functions and more

Case, SQL Join

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 78
Accepted Solution

Case, SQL Join

Hello, 

 

May you help to corrrect the following statement ?

I tried to created variable PracticeSize based on variable num_pats during the two tables joins.  But it was not sucessful. And there is no error message. 

Thank you . 

 

proc sql;
create table ads as
select a.*, b.num_pats,
case b.num_pats
when b.num_pats <= &q_1 then 'Small'
when &q_1 < b.num_pats <= &q_3 then 'Moderate'
when b.num_pats > &q_3 then 'Large'
end as PracticeSize
from demog_f as a
left join size as b
on a.PracticeID = b.PracticeID ;
quit;


Accepted Solutions
Solution
‎01-09-2017 02:51 PM
Super User
Posts: 5,516

Re: Case, SQL Join

CASE should take WHEN clauses, but you have added an extra reference to b.num_pats right after the word CASE.

 

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Case, SQL Join

What do &q_1 and &q_3 resolve to?

 

Run the program with MPRINT and SYMBOLGEN on and check the log.

 

options symbolgen mprint;

proc sql;
create table ads as
select a.*, b.num_pats,

case 
when b.num_pats <= &q_1 then 'Small'
when &q_1 < b.num_pats <= &q_3 then 'Moderate'
when b.num_pats > &q_3 then 'Large'
end as PracticeSize

from demog_f as a
left join size as b
on a.PracticeID = b.PracticeID ;
quit;

proc freq data=ads;
table practiceSize;
run;

Also, what does not successful mean? That doesn't tell us what isn't working. 

 

Frequent Contributor
Frequent Contributor
Posts: 78

Re: Case, SQL Join

No Error in log, however, resulted generated was wrong,

 

Practice Cumulative
Size Frequency Frequency
-----------------------------------
            5404   5404
Small   7 5411

 

 

84 proc sql;
585 create table ads as
586 select a.*, b.num_pats, 'cisplatin' as cohort ,
587 case b.num_pats
588 when b.num_pats <= &q_1 then 'Small'
SYMBOLGEN: Macro variable Q_1 resolves to 8
589 when &q_1 < b.num_pats <= &q_3 then 'Moderate'
SYMBOLGEN: Macro variable Q_1 resolves to 8
SYMBOLGEN: Macro variable Q_3 resolves to 38
590 when b.num_pats
SYMBOLGEN: Macro variable Q_3 resolves to 38
590! > &q_3 then 'Large'
591 end as PracticeSize
592 from drug1 as a
593 left join size as b
594 on a.PracticeID = b.PracticeID ;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will
result in a missing value for the CASE expression.
NOTE: Table WORK.ADS created, with 5411 rows and 34 columns.

595 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.12 seconds
cpu time 0.16 seconds


596 proc freq data = ads; tables PracticeSize ; run ;

NOTE: There were 5411 observations read from the data set WORK.ADS.
NOTE: The PROCEDURE FREQ printed page 7.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds


NOTE: Remote submit to D.__4018 complete.

Super User
Posts: 19,855

Re: Case, SQL Join

How is it incorrect? 

Solution
‎01-09-2017 02:51 PM
Super User
Posts: 5,516

Re: Case, SQL Join

CASE should take WHEN clauses, but you have added an extra reference to b.num_pats right after the word CASE.

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 209 views
  • 0 likes
  • 3 in conversation