BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ivy
Quartz | Level 8 Ivy
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Reeza
Super User

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. 

 

Ivy
Quartz | Level 8 Ivy
Quartz | Level 8

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.

Reeza
Super User

How is it incorrect? 

Astounding
PROC Star

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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