LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2003, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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


Back to: Top of message | Previous page | Main SAS-L page