SAS PROC SQL CASE条件值转换语句报错求助及修正

错误原因 #

你在多条件分支的CASE语句中重复使用了CASE关键字，SAS PROC SQL的CASE结构要求仅在开头写一次CASE，后续通过WHEN...THEN依次定义分支条件，重复写CASE会触发语法解析错误。

修正后的代码 #

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分支写法：单个CASE开头，多个WHEN...THEN
          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' 
              -- 可选：添加ELSE处理未匹配的分类，避免缺失值
              ELSE 'UNKNOWN' 
          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;

关键说明 #

• 所有分支条件嵌套在同一个CASE块内，仅在开头写一次CASE，结尾写一次END
• 建议添加ELSE子句，处理不在你定义范围内的COUNTERPARTY_CATEGORY值，避免生成缺失值
• 保持GROUP BY和ORDER BY逻辑不变，确保聚合和排序行为符合预期

内容的提问来源于stack exchange，提问作者Christoffer