Multiple case when statement

[Sandeep77]

Hi all,

I am writing a code with multiple case when statement but I am getting error as 'Expecting an END'. Can you please check the code and let me know what changes do I need to make in the code. I did tried writing end as mode of payment after every case when statement but it only gave me DD as Direct Debit and rest of the option were blank. Thanks

Proc sql;
create table Payment_method as 
select a.*,
b.dt_method_a as payment_method,
case when payment_method = 'DD' then 'Direct Debit',
case when payment_method = 'AP' or 'Cash' or 'GIRO' or 'LIVR' or 'PDQ' or 'SP' or 'ZAUT' then 'Cash',
case when payment_method = 'CC' then 'Credit Card',
case when payment_method = 'DC' then 'Debit Card',
case when payment_method = 'PO' then 'Postal order',
case when payment_method = 'SO' then 'Standing order'
else 'No_info' end as Mode_of_payment

from Adding_collections as a
Inner join p2scflow.debt as b on a.debt_code=b.debt_code;
quit;

Log:
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         Proc sql;
30         create table Payment_method as
31         select a.*,
32         b.dt_method_a as payment_method,
33         case when payment_method = 'DD' then 'Direct Debit',
                                                              _
                                                              73
34         case when payment_method = 'AP' or 'Cash' or 'GIRO' or 'LIVR' or 'PDQ' or 'SP' or 'ZAUT' then 'Cash',
                                                                                                               _
                                                                                                               73
35         case when payment_method = 'CC' then 'Credit Card',
                                                             _
                                                             73
36         case when payment_method = 'DC' then 'Debit Card',
                                                            _
                                                            73
37         case when payment_method = 'PO' then 'Postal order',
                                                              _
                                                              73
ERROR 73-322: Expecting an END.

38         case when payment_method = 'SO' then 'Standing order'
39         else 'No_info' end as Mode_of_payment
40         
41         from Adding_collections as a
42         Inner join p2scflow.debt as b on a.debt_code=b.debt_code;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43         quit;
NOTE: The SAS System stopped processing this step because of errors.
2                                                          The SAS System                           09:12 Tuesday, November 22, 2022

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              345.78k
      OS Memory           26712.00k
      Timestamp           11/22/2022 02:34:39 PM
      Step Count                        10  Switch Count  0
      
44         
45         %LET _CLIENTTASKLABEL=;
46         %LET _CLIENTPROCESSFLOWNAME=;
47         %LET _CLIENTPROJECTPATH=;
48         %LET _CLIENTPROJECTPATHHOST=;
49         %LET _CLIENTPROJECTNAME=;
50         %LET _SASPROGRAMFILE=;
51         %LET _SASPROGRAMFILEHOST=;
52         
53         ;*';*";*/;quit;run;
54         ODS _ALL_ CLOSE;
55         
56         
57         QUIT; RUN;
58         

[LinusH]

Log is clear, you need to add END statements with CASE, check the documentation for details.

[Sandeep77]

Yes, that's what I did like case when payment_method = 'DD' then 'Direct Debit' end as Mode_of_payment,
case when payment_method = 'AP' or 'Cash' or 'GIRO' or 'LIVR' or 'PDQ' or 'SP' or 'ZAUT' then 'Cash' end as Mode_of_payment, and so on but then in the mode of payment it is just showing DD as Direct Debit and other options are showing as blank.

[LinusH]

I should have only one CASE statement and multiple WHEN, no commas are allowed within a single column expression.

[Reeza]

Proc sql;
create table Payment_method as 
select a.*,
b.dt_method_a as payment_method,
case when payment_method = 'DD' then 'Direct Debit'
when payment_method in( 'AP' , 'Cash',  'GIRO', 'LIVR', 'PDQ', 'SP' , 'ZAUT' ) then 'Cash'
when payment_method = 'CC' then 'Credit Card'
when payment_method = 'DC' then 'Debit Card'
when payment_method = 'PO' then 'Postal order'
when payment_method = 'SO' then 'Standing order'
else 'No_info' end as Mode_of_payment

from Adding_collections as a
Inner join p2scflow.debt as b on a.debt_code=b.debt_code;
quit;

• CASE should only be in the statement once
• No comma's at end
• OR statement is incorrect, need to either list all variables or use IN (in is easier)

---

@Sandeep77 wrote:

Hi all,

I am writing a code with multiple case when statement but I am getting error as 'Expecting an END'. Can you please check the code and let me know what changes do I need to make in the code. I did tried writing end as mode of payment after every case when statement but it only gave me DD as Direct Debit and rest of the option were blank. Thanks

Proc sql;
create table Payment_method as 
select a.*,
b.dt_method_a as payment_method,
case when payment_method = 'DD' then 'Direct Debit',
case when payment_method = 'AP' or 'Cash' or 'GIRO' or 'LIVR' or 'PDQ' or 'SP' or 'ZAUT' then 'Cash',
case when payment_method = 'CC' then 'Credit Card',
case when payment_method = 'DC' then 'Debit Card',
case when payment_method = 'PO' then 'Postal order',
case when payment_method = 'SO' then 'Standing order'
else 'No_info' end as Mode_of_payment

from Adding_collections as a
Inner join p2scflow.debt as b on a.debt_code=b.debt_code;
quit;

Log:
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28         
29         Proc sql;
30         create table Payment_method as
31         select a.*,
32         b.dt_method_a as payment_method,
33         case when payment_method = 'DD' then 'Direct Debit',
                                                              _
                                                              73
34         case when payment_method = 'AP' or 'Cash' or 'GIRO' or 'LIVR' or 'PDQ' or 'SP' or 'ZAUT' then 'Cash',
                                                                                                               _
                                                                                                               73
35         case when payment_method = 'CC' then 'Credit Card',
                                                             _
                                                             73
36         case when payment_method = 'DC' then 'Debit Card',
                                                            _
                                                            73
37         case when payment_method = 'PO' then 'Postal order',
                                                              _
                                                              73
ERROR 73-322: Expecting an END.

38         case when payment_method = 'SO' then 'Standing order'
39         else 'No_info' end as Mode_of_payment
40         
41         from Adding_collections as a
42         Inner join p2scflow.debt as b on a.debt_code=b.debt_code;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43         quit;
NOTE: The SAS System stopped processing this step because of errors.
2                                                          The SAS System                           09:12 Tuesday, November 22, 2022

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              345.78k
      OS Memory           26712.00k
      Timestamp           11/22/2022 02:34:39 PM
      Step Count                        10  Switch Count  0
      
44         
45         %LET _CLIENTTASKLABEL=;
46         %LET _CLIENTPROCESSFLOWNAME=;
47         %LET _CLIENTPROJECTPATH=;
48         %LET _CLIENTPROJECTPATHHOST=;
49         %LET _CLIENTPROJECTNAME=;
50         %LET _SASPROGRAMFILE=;
51         %LET _SASPROGRAMFILEHOST=;
52         
53         ;*';*";*/;quit;run;
54         ODS _ALL_ CLOSE;
55         
56         
57         QUIT; RUN;
58         

---

[Tom · Accepted solution]

You appear to only be creating one variable. So you should only need one CASE clause.

Also it does not make any sense to place the boolean operator OR between two string constants.  I suspect you want to use the IN operator instead.

proc sql;
create table Payment_method as 
select a.*
     , b.dt_method_a as payment_method
     , case when payment_method = 'DD' then 'Direct Debit'
            when payment_method in ('AP' 'Cash' 'GIRO' 'LIVR' 'PDQ' 'SP' 'ZAUT') then 'Cash'
            when payment_method = 'CC' then 'Credit Card'
            when payment_method = 'DC' then 'Debit Card'
            when payment_method = 'PO' then 'Postal order'
            when payment_method = 'SO' then 'Standing order'
            else 'No_info'
        end as Mode_of_payment
from Adding_collections as a
inner join p2scflow.debt as b
   on a.debt_code=b.debt_code
;
quit;

[Sandeep77]

Thank you for  clarifying it.