topic Re: Proc SQL Case Statement in SAS Programming

Proc SQL Case Statement

anonymous_user — Wed, 27 Aug 2014 09:45:09 GMT

I need support with my case statement please. I have a calcualted field within the CASE statement but I can't seem to get it to work?

This is my error message -

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND,

BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, THEN, ^,

^=, |, ||, ~, ~=.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

proc sql;

create table work.dtv as

select
distinct month_date,
balance_outstanding as balance,
curr_index_val as val,
avg (curr_index_val) as avg_val,
round(balance/val,0.00001) as dtv format percent8.2,
case
when calculated dtv <30 then 'Less than 30%'
when calcualted dtv =>30 and <50 then '30%-50%'
when calcualted dtv =>50 and <60 then '50%-60%'
when calculated dtv =>60 and <75 then '60%-75%'
when calculated dtv =>75 and <95 then '75%-95%'
when calculated dtv =>95 and <100 then '95%-100%'
else '100%+' end as avedtv

from
gbasel.baseljul14
where optimum_platform = 'Optimum'
and Arrears_lit_stage_code not in ('L4','L5','L6')

group by
month_date, avedtv;

quit;

Re: Proc SQL Case Statement

RW9 — Wed, 27 Aug 2014 09:56:33 GMT

Hi,

Am just thinking we have been over this code a few times before. There is a syntax error in the when statements (calculated spelt incorrectly, you cannot do ranges just with an And like that):

when calcualted dtv =>30 and <50 then '30%-50%'

Change to: when CALCULATED 30 <= dtv < 50 then '30%-50%'

Or: when CALCULATED dtv =>30 and CALCULATED dtv <50 then '30%-50%'

It is easier to debug if you split your code out.

Re: Proc SQL Case Statement

jakarman — Wed, 27 Aug 2014 10:00:14 GMT

It will be probably more easy to apply a format.

Re: Proc SQL Case Statement

anonymous_user — Wed, 27 Aug 2014 10:12:11 GMT

Im now getting this error message for the 2 options used above -

when CALCULATED 30 <= dtv < 50 then '30%-50%'

ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

when CALCULATED dtv =>30 and CALCULATED dtv <50 then '30%-50%'

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

Re: Proc SQL Case Statement

RW9 — Wed, 27 Aug 2014 10:23:23 GMT

Yes, typo on my part, calculated should go before the variable.:

when 30 <= CALCULATED dtv < 50 then '30%-50%'

To note, you do not need both the options I provided. They basically show slightly different syntax, choose one or the other.

Re: Proc SQL Case Statement

anonymous_user — Wed, 27 Aug 2014 10:33:07 GMT

Still can't get it to work as required, below is my output from th code used -

dtv	avedtv
0.00%	100%+
0.02%	100%+
0.05%	100%+
0.06%	100%+
0.03%	100%+
0.07%	100%+
82.72%	100%+
55.48%	100%+
28.13%	100%+
84.26%	100%+
73.74%	100%+
77.35%	100%+

Re: Proc SQL Case Statement

RW9 — Wed, 27 Aug 2014 10:50:41 GMT

Yes, its defaulting out of the case. I can't really test without test data, however looking at the code there are several areas of concern to me, for instance you use a variable balance and val in calculation for dtv, are these in the original dataset, as you have not put calculated before them. I would again suggest that you need to break down what you are doing so that you can see each piece of the code coming together. From the code now its not clear what variables are in the dataset, some may get created as empty etc. Start by part one, getting your sums. For instance, you are grouping by a variable which doesn't exist until after the resolution of a formula requiring the calculated variable. So:

Step 1 - Decide what the groupings are in the data you have available.

Step 2 - Do your sums to create a new table.

Step 3 - Assign percentage grouping.

Perhaps post some test data, and what you expect out at the end.