topic Proc SQL Case Statement Error in SAS Programming

Proc SQL Case Statement Error

davidvalentine — Thu, 09 Jan 2020 14:19:04 GMT

Hello Beautiful People!

I keep running into a syntax error when for my last Case Statement (Case MOB), but I do not know why it's wrong. Below is the code:

proc sql;
create table MOB&YYMM. as 
select * from
	(select
		YYMM,
		MOB,
		count(a.PROD_ACCT_NO) as AcctCt,
		sum(STMT) as BalAmt,
		avg(STMT) as AVG_Bal_Amt,
		Year,
		substr(put(&YYMM.,4.),3,2) as Month_Var,
			Case calculated Month_Var
				when '01' then 'Jan'
				when '02' then 'Feb'
				when '03' then 'Mar'
				when '04' then 'Apr'
				when '05' then 'May'
				when '06' then 'Jun'
				when '07' then 'Jul'
				when '08' then 'Aug'
				when '09' then 'Sep'
				when '10' then 'Oct'
				when '11' then 'Nov'
				when '12' then 'Dec'
			End as Month,

			Case MOB
				when <13 then '1-12'
				when >=13 and <61 then '13-60'
				when >=61 and <121 then '61-120'
				when >=121 then '121+'
			End as MOB_Range
from example3 as a
group by MOB
);
run;
quit;

The log gives the following error:

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.

My end goal is to try to use this case statement to create ranges for my MOB variable. The MOB variable is an integer, and I would like the ranges to be strings. Any information on this is greatly appreciated!

-Valentine

Re: Proc SQL Case Statement Error

Kurt_Bremser — Thu, 09 Jan 2020 14:43:04 GMT

You can only use single values or complete conditions:

			Case
				when MOB < 13 then '1-12'
				when MOB >= 13 and MOB < 61 then '13-60'
				when MOB >= 61 and MOB < 121 then '61-120'
				when MOB >= 121 then '121+'
			End as MOB_Range

Re: Proc SQL Case Statement Error

unison — Thu, 09 Jan 2020 14:30:56 GMT

Just a suggestion to clean up your query. Instead of the case statement for the month_var, take the date and apply something like MONYY format and then extract the first 3 characters.

data _null_;
dt = '31MAR2019'd;
mon_var=put(put(dt,monyy.),$3.);
put _all_;
run;

Re: Proc SQL Case Statement Error

davidvalentine — Thu, 09 Jan 2020 14:43:31 GMT

Straight forward and to the point; thanks, KurtBremser.