How to turn convert values conditionally-CodePudding

I have this case expression and for some reason it fails when I run it:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_KTSTMSIK_20 AS 
   SELECT t1.COUNTERPARTY_CATEGORY, 
          (SUM(t1.SUM_of_balance)) FORMAT=COMMAX22.2 AS SUM_of_SUM_of_balance,

        CASE WHEN COUNTERPARTY_CATEGORY = 'Retail'
        THEN '0480' 
        CASE WHEN COUNTERPARTY_CATEGORY = 'NON-FINANCIAL'    
        THEN '0490'
        CASE WHEN COUNTERPARTY_CATEGORY = 'OTHER_FINANCIAL'    
        THEN '0570'
        CASE WHEN COUNTERPARTY_CATEGORY = 'BANK'    
        THEN '0530' 
        END AS Row_datalab

            
      FROM WORK.QUERY_FOR_KTSTMSIK_20_0004 t1
      GROUP BY t1.COUNTERPARTY_CATEGORY
      ORDER BY SUM_of_SUM_of_balance;
QUIT;

My goal is to convert the text to number like this:

RETAIL='0480'
NON-FINANCIAL='0490'
OTHER_FINANCIAL='0570'
BANK='0530'

Edit: This is the error message I get , sorry I forgot to ad the most important thing:

33              CASE WHEN COUNTERPARTY_CATEGORY = 'Retail'
34                 THEN '0480'
35                 CASE WHEN COUNTERPARTY_CATEGORY = 'NON-FINANCIAL'
                   ____
                   22
                   202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **,  , -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, ELSE, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, WHEN, ^, ^=, 
              |, ||, ~, ~=.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

36                 THEN '0490'
37                 CASE WHEN COUNTERPARTY_CATEGORY = 'OTHER_FINANCIAL'
                   ____
                   22
                   202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **,  , -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, 
              CONTAINS, ELSE, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, WHEN, ^, ^=, 
              |, ||, ~, ~=.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

38                 THEN '0570'

I hope you can help me.

CodePudding user response：

You used wrong case sintax. There is only one CASE statement, not one per every WHEN statement. This should work now:

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_KTSTMSIK_20 AS 
   SELECT t1.COUNTERPARTY_CATEGORY, 
          (SUM(t1.SUM_of_balance)) FORMAT=COMMAX22.2 AS SUM_of_SUM_of_balance,

        CASE 
            WHEN COUNTERPARTY_CATEGORY = 'Retail' THEN '0480' 
            WHEN COUNTERPARTY_CATEGORY = 'NON-FINANCIAL' THEN '0490'
            WHEN COUNTERPARTY_CATEGORY = 'OTHER_FINANCIAL' THEN '0570'
            WHEN COUNTERPARTY_CATEGORY = 'BANK' THEN '0530' 
        END AS Row_datalab

            
      FROM WORK.QUERY_FOR_KTSTMSIK_20_0004 t1
      GROUP BY t1.COUNTERPARTY_CATEGORY
      ORDER BY SUM_of_SUM_of_balance;
QUIT;