Date:         Tue, 27 May 2003 14:52:11 -0400
Reply-To:     Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject:      Re: Merge by period of time
Comments: To: Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Comments: cc: "pierre.thomasset@EURONET.BE" <pierre.thomasset@EURONET.BE>
Content-Type: text/plain

Howard,

Nice solution.  I have just one additional comment.  Your code depends on
the fact that each ID group separately has a common initial date (1jan2003
in each case) and a common terminal date (31dec6999 in each case) for the
two files.  It is true of the sample data, but perhaps an assumption that
should be checked if the system creating the data does not guarantee it; or
additional beginning and end records could be added to meet this
requirement.

IanWhitlock@westat.com

-----Original Message-----
From: Howard Schreier [mailto:Howard_Schreier@ITA.DOC.GOV]
Sent: Tuesday, May 27, 2003 8:30 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Merge by period of time


It's pretty natural using SQL. My tested code:

   proc sql;
   create table results as
    select t1.id,
           max(t1.dat1,t2.dat1) as dat1 format date9.,
           min(t1.dat2,t2.dat2) as dat2 format date9.,
           v11, v12, v21, v22
     from t1, t2
     where t1.id = t2.id and
           t1.dat1 <= t2.dat2 and
           t2.dat1 <= t1.dat2
    ;
   quit;

On Mon, 26 May 2003 04:54:06 -0400, Thomasset Pierre
<pierre.thomasset@EURONET.BE> wrote:

>We sometimes need to merge two tables, both containing a period of time
>in the identifier. The resulting table should split the observations
>into new values of the period of time, taking into account the fact
>that periods of times are not the same in the two tables.
>Example below :
>table t1, identifier id + dat1 + dat2, attributes v11 v12
>table t2, identifier id + dat1 + dat2, attributes v21 v22
>period of time = dat1 - dat2
>Do you have suggestions to solve this kind of merge / join ?
>Thanks.
>
>data t1;
>  input @1 id $2.
>        @4 dat1 date9.
>        @14 dat2 date9.
>        v11 v12;
>  format dat1 dat2 ddmmyy10.;
>cards;
>01 01jan2003 28feb2003 10111 10112
>01 01mar2003 31dec6999 10121 10122
>02 01jan2003 31jan2003 10211 10212
>02 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 01jan2003 31jan2003 20111 20112
>01 01feb2003 31dec6999 20121 20122
>02 01jan2003 28feb2003 20211 20212
>02 01mar2003 31dec6999 20221 20222
>;
>run;
>
>/* Results of 'merge' t1 t2, should be :
>id dat1      dat2      v11   v12   v21   v22
>01 01jan2003 31jan2003 10111 10112 20111 20112
>01 01feb2003 28feb2003 10111 10112 20121 20122
>01 01mar2003 31dec2999 10111 10112 20111 20112
>02 01jan2003 31jan2003 10211 10212 20211 20212
>02 01feb2003 28feb2003 10221 10222 20211 20212
>02 01mar2003 31dec2999 10221 10222 20221 20222
>*/