SAS/PROC-SQL从具有唯一键的表转换到具有多行的表具有相同的键

Question

目前我有一个类似下表的表格：

U_ID SPOUSEDOB   FCHILDDOB   SCHILDDOB   ChangeDate
1    20/01/1980  01/01/1900  01/01/1900  01/01/2000
2    20/01/1950  20/01/1970  01/01/1900  01/01/2000
3    20/01/1960  20/01/1990  20/01/1995  01/01/2000
1    20/01/1980  20/01/1995  01/01/1900  01/01/2005
1    20/01/1980  20/01/1995  20/01/2006  01/01/2010

日期为1900年1月1日，表示没有配偶/孩子。我想转换这个表，如下所示：

Member_ID  U_ID  Relation DOB         ChangeDate
1          1     Spouse   20/01/1980  01/01/2000
2          2     Spouse   20/01/1950  01/01/2000
3          2     Child    20/01/1970  01/01/2000
4          3     Spouse   20/01/1960  01/01/2000
5          3     Child    20/01/1990  01/01/2000
6          3     Child    20/01/1995  01/01/2000
7          1     Child    20/01/1995  01/01/2005
8          1     Child    20/01/2006  01/01/2010

但是这个表仍然不能提供在特定时间(2006年1月1日)和2011年1月1日(2011年1月1日)回答这个问题的最佳方式，用户1有几个孩子？答案是1和2。我也发现很难从表1转换到表2，我陷入了如何为相同的user_id创建新行的问题。关于如何改善这种情况或解决转换表的问题有什么想法吗？真的很感谢你的帮助。提前谢谢你。

Answer 1

下面是一个简单的SAS数据步骤。您可以调整它以使用VNAME()来定义关系(取决于其他变量的命名方式)；例如，

relation = vname(DOBs[_t]);

然后使用SUBSTR或其他工具将其缩短为适当的文本。除此之外，它应该足够灵活，可以处理初始HAVE数据集中的任意数量的关系。

data want;
set have;
array DOBs SPOUSEDOB   FCHILDDOB   SCHILDDOB;
do _t = 1 to dim(DOBs);
  if DOBs[_t] ne '01JAN1900'd then do;
    relation=ifc(_t=1,'Spouse','Child'); *this could also be done using VNAME() to be more flexible;
    DOB=DOBs[_t];
    output;
  end;
end;
keep relation DOB ChangeDate U_ID;
format DOB Changedate Date9.;
run;

proc sort data=want;
by u_id descending relation dob changedate;
run;


data final;
set want;
by u_id descending relation dob changedate;
if first.dob;
run;

然后将其处理为只选择某个日期出生的人，如果您更喜欢使用SQL，则可以使用查询fthiella posted，或者可以在SAS过程中进行过滤，例如：

proc means data=final;
where dob le '01JAN2006'd;
class relation;
var (whatever);
run;

或者使用ChangeDate，如果这是您想要过滤的内容，而不是实际的道布。

Answer 2

这会将表格从第一种格式转换为第二种格式：

SELECT
  U_ID,
  'Spouse' Relation,
  Spousedob DOB,
  MIN(STR_TO_DATE(ChangeDate, '%d/%m/%Y')) ChangeDate
FROM
  yourtable
WHERE
  Spousedob != '01/01/1900'
GROUP BY U_ID
UNION ALL
SELECT
  U_ID,
  'Child' Relation,
  FCHILDDOB DOB,
  MIN(STR_TO_DATE(ChangeDate, '%d/%m/%Y')) ChangeDate
FROM
  yourtable
WHERE FCHILDDOB != '01/01/1900'
GROUP BY U_ID
UNION ALL
SELECT
  U_ID,
  'Child' Relation,
  SCHILDDOB DOB,
  MIN(STR_TO_DATE(ChangeDate, '%d/%m/%Y')) ChangeDate
FROM yourtable
WHERE SCHILDDOB != '01/01/1900'
GROUP BY U_ID
ORDER BY ChangeDate, U_ID

但要回答您的问题，您可以使用以下查询：

SELECT (FCHILDDOB!='01/01/1900')+(SCHILDDOB!='01/01/1900')
FROM yourtable
WHERE
  (U_ID, ChangeDate) IN (
    SELECT U_ID, MAX(ChangeDate)
    FROM yourtable
    WHERE
      U_ID=1 AND MIN(STR_TO_DATE(ChangeDate, '%d/%m/%Y'))<'2011-01-01')

(我考虑将您的日期存储为varchar，并使用STR_TO_DATE将其转换为日期)

编辑

您可以创建一个包含列(Member_ID auto_increment、U_ID、Relation、道布、ChangeDate)的表yourtable2，然后使用以下命令将所有数据从yourtable插入到yourtable2：

INSERT INTO yourtable2 (U_ID, Relation, DOB, ChangeDate)
SELECT ... --- the select query above
ORDER BY ChangeDate, DOB, U_ID

然后计算你可以使用的孩子的名字：

SELECT COUNT(*)
FROM   yourtable2
WHERE  Relation='Child'
       AND U_ID=1
       AND ChangeDate <= '2011-01-01'

请参阅fiddle here。

Answer 3

这不起作用，因为我不理解您的起始表中的关系。但它可能会帮助您找到另一个使用普通老式SAS数据步骤代码的解决方案：

data have;
   input U_ID SPOUSEDOB :ddmmyy10. FCHILDDOB :ddmmyy10.  
         SCHILDDOB :ddmmyy10. ChangeDate :ddmmyy10.;
datalines;
1    20/01/1980  01/01/1900  01/01/1900  01/01/2000
2    20/01/1950  20/01/1970  01/01/1900  01/01/2000
3    20/01/1960  20/01/1990  20/01/1995  01/01/2000
1    20/01/1980  20/01/1995  01/01/1900  01/01/2005
1    20/01/1980  20/01/1995  20/01/2006  01/01/2010
run;
data want(keep=Member_ID U_ID Relation DOB ChangeDate);
   attrib Member_ID  length=8;
   attrib U_ID       length=8;
   attrib Relation   length=$6;
   attrib DOB        length=8 format=ddmmyy10.;
   attrib ChangeDate length=8 format=ddmmyy10.;
   retain Member_ID 0;

   set have;

   if _n_ = 1 or U_ID ne 1 then do;
      Member_ID + 1;
      Relation = 'Spouse';
      DOB = SPOUSEDOB;
      output;
      end;

   if FCHILDDOB ne mdy(1,1,1900) then do;
      Member_ID + 1;
      Relation = 'Child';
      DOB = FCHILDDOB;
      output;
      end;
   if SCHILDDOB ne mdy(1,1,1900) then do;
      Member_ID + 1;
      Relation = 'Child';
      DOB = SCHILDDOB;
      output;
      end;
  run;