01-28-2015 09:42 PM
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
01-29-2015 01:52 AM
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.
01-29-2015 07:47 AM
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.
01-29-2015 08:16 AM
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.