Date: Wed, 28 May 2003 09:19:56 -0400
Reply-To: Thomasset Pierre <pierre.thomasset@EURONET.BE>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Thomasset Pierre <pierre.thomasset@EURONET.BE>
Subject: Re: Merge by period of time
I do agree with the last remarks of Ian and Howard (thanks a lot).
So, one solution should be to fill all the gaps in the time dimension, in
order to always match the SQL logic.
Example :
data t1;
input @1 id $2.
@4 dat1 date9.
@14 dat2 date9.
v11 v12;
format dat1 dat2 ddmmyy10.;
cards;
01 . 30nov2002 . .
01 01dec2002 28feb2003 10111 10112
01 01mar2003 31dec6999 10121 10122
02 . 31dec2002 . .
02 01jan2003 31jan2003 10211 10212
02 01feb2003 31dec6999 10221 10222
03 . 31oct2002 . .
03 01nov2002 28feb2003 10111 10112
03 01mar2003 31dec6999 10121 10122
05 . 31dec2002 . .
05 01jan2003 31jan2003 10211 10212
05 01feb2003 31dec6999 10221 10222
;
run;
data t2;
input @1 id $2.
@4 dat1 date9.
@14 dat2 date9.
v21 v22;
format dat1 dat2 ddmmyy10.;
cards;
01 . 31dec2002 . .
01 01jan2003 31jan2003 20111 20112
01 01feb2003 31dec6999 20121 20122
02 . 31dec2002 . .
02 01jan2003 28feb2003 20211 20212
02 01mar2003 31dec6999 20221 20222
04 . 31dec2002 . .
04 01jan2003 31jan2003 20111 20112
04 01feb2003 31dec6999 20121 20122
05 . 30sep2002 . .
05 01oct2002 28feb2003 20211 20212
05 01mar2003 31dec6999 20221 20222
;
run;
proc sql;
create table results2 as
select coalesce (t1.id, t2.id) as id,
max (t1.dat1, t2.dat1) as dat1 format ddmmyy10.,
min (t1.dat2, t2.dat2) as dat2 format ddmmyy10.,
v11, v12, v21, v22
from v1 as t1
full join
v2 as t2
on t1.id = t2.id
where (t1.dat1 <= t2.dat2 or t2.dat2 = .) and
(t2.dat1 <= t1.dat2 or t1.dat2 = .)
order by id, dat1, dat2
;
quit;
options nonumber nodate nocenter;
title;
proc print data= results2;
id id;
by id;
run;
id dat1 dat2 v11 v12 v21 v22
01 30/11/2002
01/12/2002 31/12/2002 10111 10112
01/01/2003 31/01/2003 10111 10112 20111 20112
01/02/2003 28/02/2003 10111 10112 20121 20122
01/03/2003 31/12/6999 10121 10122 20121 20122
02 31/12/2002
01/01/2003 31/01/2003 10211 10212 20211 20212
01/02/2003 28/02/2003 10221 10222 20211 20212
01/03/2003 31/12/6999 10221 10222 20221 20222
03 31/10/2002
01/11/2002 28/02/2003 10111 10112
01/03/2003 31/12/6999 10121 10122
04 31/12/2002
01/01/2003 31/01/2003 20111 20112
01/02/2003 31/12/6999 20121 20122
05 30/09/2002
01/10/2002 31/12/2002 20211 20212
01/01/2003 31/01/2003 10211 10212 20211 20212
01/02/2003 28/02/2003 10221 10222 20211 20212
01/03/2003 31/12/6999 10221 10222 20221 20222
