topic Re: Syntax error multiple conditions in SAS Programming

Syntax error multiple conditions

Babloo — Sat, 22 Aug 2020 18:50:02 GMT

I'm looking for some help to resolve the below Syntax error. Tried placing the paranthesis in different places but it didn't help.

Code which I used is,

proc sql;
    select id,name,
        (case 
            when Alloc=0 then . 
            else if (Alloc=1 and Cal_Mon=REPORTING_DT) then  
            ((Quar_Prem*Paid_Expenses))
            else if  (Alloc=1 and Cal_Mon>=REPORTING_DT) then 
            ((Quar_Prem*sum(Expenses+actor)))
            else . 
        end)
    as Expense length = 8
        format = 21.4
    from have;
quit;

Log:

26         proc sql;
27             select id,name,
28                 (case
29                     when Alloc=0 then .
30                     else if (Alloc=1 and Cal_Mon=REPORTING_DT) then
                                                                  ____
                                                                  22
                                                                  76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

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

31                     ((Quar_Prem*Paid_Expenses))
32                     else if  (Alloc=1 and Cal_Mon>=REPORTING_DT) then
33                     ((Quar_Prem*sum(Expenses+actor)))
34                     else .
35                 end)
36             as Expense length = 8
37                 format = 21.4
38             from have;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
39         quit;
NOTE: The SAS System stopped processing this step because of errors

Re: Syntax error multiple conditions

Kurt_Bremser — Sat, 22 Aug 2020 18:57:48 GMT

SQL does not know IF-THEN-ELSE, you have to use CASE-WHEN-<WHEN->ELSE-END for every condition.

Re: Syntax error multiple conditions

Babloo — Sat, 22 Aug 2020 19:18:21 GMT

Thank you for letting me know. How to tackle with parenthesis? May I
request to check the whether the parenthesis are placed correctly in the
posted example?

Re: Syntax error multiple conditions

Tom — Sat, 22 Aug 2020 19:30:46 GMT

Just use more WHEN clauses.

proc sql;
select id
     , name
     , case when (Alloc=0) then . 
            when (Alloc=1 and Cal_Mon=REPORTING_DT) then Quar_Prem*Paid_Expenses
            when (Alloc=1 and Cal_Mon>=REPORTING_DT) then Quar_Prem*sum(Expenses+actor)
            else . 
       end as Expense format = 21.4
from have
;
quit;

What is that last value supposed to be?

Quar_Prem*sum(Expenses+actor)

Does EXPENSES or ACTOR ever have missing values? Did you want to use SAS's SUM(,) function so that missing values are ignored?

Quar_Prem*sum(sum(Expenses,actor))

You don't have any group by, did you really want to use the aggregate sum of the addition of those two variables over the whole dataset so that the multiplying factor on each observation is the same? Did you want to add a GROUP BY clause so the multiplying factor is just constant for the observations in the same group?

Or did you want to remove the SQL aggregate SUM() function so that there is a different multiplying factor on each observation?

Re: Syntax error multiple conditions

novinosrin — Sat, 22 Aug 2020 19:30:35 GMT


proc sql;
    select id,name,
        (case 
            when Alloc=0 then . 
            when Alloc=1 and Cal_Mon=REPORTING_DT then  
            Quar_Prem*Paid_Expenses
            when (Alloc=1 and Cal_Mon>=REPORTING_DT) then 
            ((Quar_Prem*sum(Expenses,actor)))
            else . 
        end)
    as Expense length = 8
        format = 21.4
    from have;
quit;

Re: Syntax error multiple conditions

Reeza — Sat, 22 Aug 2020 21:15:57 GMT

You used IF but SQL wants WHEN instead. That's the primary cause of your mistake, not parenthesis.