PROC MERGE? or another function?

[Kristinos]

Hello!

I am trying to merge two datasets, but I am not sure if what I want it is possible?

I want the DATE (in data2) to fit within the correct period (in data1), which has a start_date and an end_date…

Data1:

ID        Period   Start_date       End_date

10        1          01.01.2012      31.12.2013

10        2          01.01.2013      31.12.2014

10        3          01.01.2014      31.12.2015

20        1          01.06.2012      31.05.2013

20        2          01.06.2013      31.05.2014

Data2:

ID        DATE              YIELD

10        01.03.2013      20

10        01.05.2013      30

10        01.10.2014      50

10        01.11.2015      60

20        01.09.2013      70

This is how I want the output to look like:

ID        Number  Start_date       End_date       DATE             YIELD

10        1          01.01.2012      31.12.2013      01.03.2013      20

10        1          01.01.2012      31.12.2013      01.05.2013      30

10        2          01.01.2013      31.12.2014      01.10.2014      50

10        3          01.01.2014      31.12.2015      01.11.2015      60

20        1          01.06.2012      31.05.2013      .           .

20        2          01.06.2013      31.05.2014      01.09.2013      70

So far my SAS-skills haven't succeed, hopefully there are someone out there who could help?

Thank you!

[Reeza]

You can use SQL join where the join is using a between. One of the few cases where SQL is better than a data step.

untested:

proc sql;

create table want as

select a.*, b.*

from data1 as a

full join data2 as b

on b.date between a.start_date and b.end_date;

quit;

[Kristinos]

Amazing how easy it can be done... Should have learned SQL, but there is no time...

THANK YOU VERY MUCH!!

[art297 · Accepted solution]

I disagree that you want a full join. However, I also think you are missing some matches that exist in your data. I suggest trying the following:

proc sql noprint;

  create table want as

    select *

      from data1 a

        left join data2 b

          on a.id=b.id

            where a.start_date<=b.date<=a.end_date

  ;

quit;

[Reeza]

There's an obs in Data2 that is in the final file, so I assumed full join...I'm assuming the OP's post is correct of course

[Kristinos]

You are right Arthur Tabachneck.

With the first version all data in data2 were matched with both ID,

but your SQL seems to work.

THANK YOU!!