sql - PROC SQL - 满足条件时将数据转置到列中

Question

我的数据结构如下，其中每个唯一 ID 都有一行显示该月最后一天的余额:

ID      Day_Key    Balance
23412   20171229   50000
23412   20180131   45000
23412   20180228   40000   
27435   20171229   100000    
27435   20180131   80000
27435   20180228   60000

我想创建一个表，其中每个唯一 ID 显示在一行中，列表示每个月的余额，如下所示:

ID     DEC17    JAN 18    FEB18
23412  50000    45000     40000
27435  100000   80000     60000

**更新*

我当前的代码如下所示

PROC SQL;
CREATE TABLE BAL_TRANSPOSE AS 
SELECT DISTINCT ID,
        MAX(SUB_EY17) AS EY17,
      MAX(SUB_JAN18) AS JAN18,
      MAX(SUB_FEB18) AS FEB18,
      MAX(SUB_MAR18) AS MAR18,
      MAX(SUB_APR18) AS APR18,
      MAX(SUB_MAY18) AS MAY18,
      MAX(SUB_JUN18) AS JUN18,
      MAX(SUB_JUL18) AS JUL18,
      MAX(SUB_AUG18) AS AUG18,
      MAX(SUB_SEP18) AS SEP18,
      MAX(SUB_OCT18) AS OCT18,
      MAX(SUB_NOV18) AS NOV18,
      MAX(SUB_EY18) AS EY18
FROM (SELECT DISTINCT ID,
    CASE WHEN DAY_KEY = 20171229 THEN OUTSTANDING_BALANCE END AS SUB_EY17,
    CASE WHEN DAY_KEY = 20180131 THEN OUTSTANDING_BALANCE END AS SUB_JAN18,
    CASE WHEN DAY_KEY = 20180228 THEN OUTSTANDING_BALANCE END AS SUB_FEB18,
    CASE WHEN DAY_KEY = 20180330 THEN OUTSTANDING_BALANCE END AS SUB_MAR18,
    CASE WHEN DAY_KEY = 20180430 THEN OUTSTANDING_BALANCE END AS SUB_APR18,
    CASE WHEN DAY_KEY = 20180531 THEN OUTSTANDING_BALANCE END AS SUB_MAY18,
    CASE WHEN DAY_KEY = 20180629 THEN OUTSTANDING_BALANCE END AS SUB_JUN18,
    CASE WHEN DAY_KEY = 20180731 THEN OUTSTANDING_BALANCE END AS SUB_JUL18,
    CASE WHEN DAY_KEY = 20180831 THEN OUTSTANDING_BALANCE END AS SUB_AUG18,
    CASE WHEN DAY_KEY = 20180928 THEN OUTSTANDING_BALANCE END AS SUB_SEP18,
    CASE WHEN DAY_KEY = 20181031 THEN OUTSTANDING_BALANCE END AS SUB_OCT18,
    CASE WHEN DAY_KEY = 20181130 THEN OUTSTANDING_BALANCE END AS SUB_NOV18,
    CASE WHEN DAY_KEY = 20181231 THEN OUTSTANDING_BALANCE END AS SUB_EY18
FROM TABLE1) AS SUB
GROUP BY ID;   
QUIT;

新列已创建，但仅显示空值。下面是我看到的结果(为了便于阅读而修剪)。查询返回超过 100 万条记录，但据我所知，所有记录的值均为 0。我测试过数据，知道每个 ID 都应该有每个 day_key 的值。

ID      EY17    JAN18        FEB18       MAR18         APR18   
1111    -       -            -            -            -
2222    -       -            -            -            -
3333    -       -            -            -            -
4444    -       -            -            -            -
5555    -       -            -            -            -

Answer 1

原始 SQL 将使用聚合函数，因为该过程称为条件聚合，这是一种在列已知且数量很少时将数据从长到宽旋转的常见形式。

PROC SQL;
   CREATE TABLE BAL_TRANSPOSE AS 
   SELECT ID,
          MAX(CASE WHEN DAY_KEY = 20171229 THEN BALANCE END) AS DEC17,
          MAX(CASE WHEN DAY_KEY = 20180131 THEN BALANCE END) AS JAN18,
          MAX(CASE WHEN DAY_KEY = 20180228 THEN BALANCE END) AS FEB18
   FROM TABLE1
   GROUP BY ID    
QUIT;

但是，对于 SAS proc sql，您可能需要使用子查询:

PROC SQL;
   CREATE TABLE BAL_TRANSPOSE AS 
   SELECT ID, 
          MAX(SUB_DEC17) AS DEC17,
          MAX(SUB_JAN18) AS JAN18,
          MAX(SUB_FEB18) AS FEB18
   FROM (SELECT ID,
                CASE WHEN DAY_KEY = 20171229 THEN BALANCE END AS SUB_DEC17,
                CASE WHEN DAY_KEY = 20180131 THEN BALANCE END AS SUB_JAN18,
                CASE WHEN DAY_KEY = 20180228 THEN BALANCE END AS SUB_FEB18
         FROM TABLE1) AS sub
   GROUP BY ID    
QUIT;

实际上，您的原始查询应该出错，因为您在 SELECT 中包含了未出现在 GROUP BY 中的非聚合列 - 这违反了 ANSI-SQL 标准。 SAS 可能将您尝试的聚合查询转换为单位级别(即忽略 GROUP BY)，可能会显示日志注释或警告。