Nested case statement in sas DI sql transformation

Reply
New Contributor dfn
New Contributor
Posts: 2

Nested case statement in sas DI sql transformation

I am using a nested case statement in a DI job.  base on each parameter in a macro variable  for example C1   when a  product is between 1 and 30 days overdue then several conditions are performed. The problem I am having are in the last two

conditions in the program which give an error message when the code is executed.  Error message states  add  OR, then, ELSE,  , / * .       COR  is  macro variable used for lookup of different departments for example  C2, EC,CM, SL, GA, and GE represent different departments. STD is the company name  for example STD_&COR is STD_C1 , and STD_C2 . Macro variable  "&valid_from_dttm"dt  is the valid date for when a transaction started for example 20jun2013 01:00:00.  AND maturity_dt is maturity date for  example 20jul2014 .  The SYNTAX in BOLD is the problem.

Here my code 

CASE WHEN "&COR" in ("C1", "C2", "EC","CM","SL")  OR "&COR" in ( "GA", GE") AND  STD_&COR...product eq "coffee" then

case when ((STD_&COR..Maturity_dt  - depart("&valid_from_dttm"dt ) between 1 and 30)) then STD_&COR..cost_amt

when ((STD_&COR..payment_due  - depart("&valid_from_dttm"dt ) between 1 and 30)) then STD_&COR..cost_amt


ELSE 0

END

END

Super User
Posts: 5,260

Re: Nested case statement in sas DI sql transformation

It's hard to uncover the problem without knowing what the macro variables transform to.TTry use the PROC SQL option feedback and global option SYMBOLGEN for help in troubleshooting.

Data never sleeps
Contributor
Posts: 32

Re: Nested case statement in sas DI sql transformation

An alternative way to solve problems of this kind, which is usually easier to understand, is to create one or more lookup tables with your conditions and some result or control values and then to join on them. In your example you would have one row for C1, C2, etc. You can also have a range and use two join conditions. An outer join is better if you are not sure you have included all the conditions in your tables. The lookup tables are usually very small so are held in memory and are very fast.

Super User
Super User
Posts: 7,430

Re: Nested case statement in sas DI sql transformation

Well, I can see several potential problems there.  Firstly this bit of code: STD_&COR...product, will cause issues as you have 3 dots, one finished the macro variable, one delimits library/variable, the other is and error.  Secondly you are subtracting a date/time variable from a date variable.  Thirdly there is no clear indication of logic order in the when clause,

WHEN "&COR" in  ("C1", "C2", "EC","CM","SL")  OR  "&COR" in ( "GA", GE") AND  Firstl eq "coffee" then

so is the AND part of the second in or the whole?

There could of course be numerous other issues depending on the macro variables used.

I also agree with Peter.L, using lookup tables/joining/querying, or even generating code would be easy to read.  Perhaps provide some test data/required output.

Ask a Question
Discussion stats
  • 3 replies
  • 472 views
  • 0 likes
  • 4 in conversation