topic Case, SQL Join in SAS Programming

Case, SQL Join

Ivy — Mon, 09 Jan 2017 18:35:30 GMT

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;

Re: Case, SQL Join

Reeza — Mon, 09 Jan 2017 18:44:12 GMT

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.

Re: Case, SQL Join

Ivy — Mon, 09 Jan 2017 18:57:06 GMT

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.

Re: Case, SQL Join

Reeza — Mon, 09 Jan 2017 19:11:30 GMT

How is it incorrect?

Re: Case, SQL Join

Astounding — Mon, 09 Jan 2017 19:15:14 GMT

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