BookmarkSubscribeRSS Feed

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;

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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

 

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

--

22

76

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%'

22

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

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

dtvavedtv
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%+
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jakarman
Barite | Level 11

It will be probably  more easy to apply a format.

---->-- ja karman --<-----

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
  • 6 replies
  • 2247 views
  • 0 likes
  • 3 in conversation