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;
CASE should take WHEN clauses, but you have added an extra reference to b.num_pats right after the word CASE.
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.
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.
How is it incorrect?
CASE should take WHEN clauses, but you have added an extra reference to b.num_pats right after the word CASE.
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.