Date:         Tue, 27 May 2003 08:29:56 -0400
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
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
>*/