topic Re: Multiple case when statement in New SAS User

Multiple case when statement

Sandeep77 — Tue, 22 Nov 2022 14:37:48 GMT

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

Re: Multiple case when statement

LinusH — Tue, 22 Nov 2022 14:51:45 GMT

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

Re: Multiple case when statement

Sandeep77 — Tue, 22 Nov 2022 15:03:29 GMT

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.

Re: Multiple case when statement

LinusH — Tue, 22 Nov 2022 16:28:02 GMT

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

Re: Multiple case when statement

Reeza — Tue, 22 Nov 2022 16:38:38 GMT

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,

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

Re: Multiple case when statement

Tom — Tue, 22 Nov 2022 17:06:32 GMT

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;

Re: Multiple case when statement

Sandeep77 — Tue, 22 Nov 2022 17:03:42 GMT

Thank you for clarifying it.