BookmarkSubscribeRSS Feed
marleeakerson
Calcite | Level 5

hello, 

 

i am having trouble understanding the syntax in this proc sql step. 

 

My questions are 

1) I am not sure how to figure out where the a vs. b vs. c datasets are coming from. A lot of the variables listed under the select step are not in the OLDTABLE dataset, so i dont know where to look for them. 

2) this step keeps ending early in the log due to an error and i am not sure why: a.RECIP_ID=b.RECIP_ID

thank you! 

 

proc sql; 
	create table NEWTABLE as
	select b.var1 
	     , 2016 as year  
	     , c.var2 
	     , c.var3 
             , c.var4  
	     , c.var5 
	     , '-' as  var6 
	     , c.var7 
, a.var8 , a.var9 , a.var10 from (select distinct orid , Name as facility , cnty as county , ercategory , count(*) as visits from OLDTABLE group by 1, 2, 3, 4 ) as a left join (select distinct orid, case when count(*) eq 1 then '1 Visit' when count(*) eq 2 then '2 Visits' when count(*) >2 and count(*)<6 then '3 to 5 Visits' when count(*) >5 and count(*)<11 then '6 to 10 Visits' when count(*) >10 and count(*)<26 then '11 to 25 Visits' when count(*) >25 then '26 + Visits' else 'No Visits' end as ed_freq_cat from OLDTABLE group by 1 ) as b on a.RECIP_ID=b.RECIP_ID left join (select * from sdelig.Member_month_rolloff where MBR_MTH_YR_MTH between '201607' and '201706') as c on a.RECIP_ID=c.RECIP_ID; quit;

 

1 REPLY 1
PGStats
Opal | Level 21

Post the log please.

PG

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 325 views
  • 0 likes
  • 2 in conversation