我正在尝试对两个表中的数据进行排序,这两个表将表1中的关闭日期与表2中的最近日期(如果存在)配对。可能存在来自表1的ID日期,其在表2中没有匹配,反之亦然。也不能保证是1:1匹配。例如,我从t1获得的数据是这样的:
ID time1
A 01/09/2015
A 02/16/2015
B 03/03/2015
C 04/01/2015
C 01/20/2015
C 03/15/2015类似地,t2:
ID time2
A 01/29/2015
A 02/19/2015
B 03/06/2015
C 01/27/2015
C 03/18/2015
C 04/04/2015我不想在加入ID时是所有唯一日期的组合,而不是天数间隔最小的组合,例如。
desired_output:
ID date_time1 date_time2
A 01/09/2015 01/29/2015
A 02/16/2015 02/19/2015
B 03/03/2015 03/06/2015
C 01/20/2015 01/27/2015
C 03/15/2015 03/18/2015
C 04/01/2015 04/04/2015我不确定如何做到这一点,我已经尝试选择最大和最小,但只给出了一些东西,这不是我想要的:
ID date_time1 date_time2
A 01/09/2015 02/19/2015
B 03/03/2015 03/06/2015
C 01/20/2015 04/04/2015任何关于如何做到这一点的想法/建议都将不胜感激!谢谢!
发布于 2016-07-27 02:53:07
我使用了两个内置的SAS数据集作为示例,但这里的一般思想是,您希望将t1中的组中的每个日期与t2中相同id组中的每个日期进行比较,确定最小日期差异,并只保留那个日期。
data t1 (keep = id date rename = date = time1);
set sasuser.admitjune;
if mod(_n_, 2) = 0 then id = "A";
else id = "B";
run;
data t2 (keep = id date rename = date = time2);
set sasuser.admitjune;
if mod(_n_, 2) = 0 then id = "B";
else id = "A";
date = date + 3; /*adjusting the dates so the datasets aren't the same*/
run;为此,您可以按日期和结点的升序差异进行排序,以仅保留最近的日期和结点:
proc sql;
create table desired_output as select
a.id, a.time1, b.time2 as nearest_time2,
abs(b.time2-a.time1) as diff
from t1 as a
left join t2 as b
on a.id = b.id and b.time2 > a.time1
group by a.id, a.time1
order by calculated diff;
proc sort nodupkey; by id time1;
quit;https://stackoverflow.com/questions/38594554
复制相似问题