PROC MERGE? or another function?

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

PROC MERGE? or another function?

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!


Accepted Solutions
Solution
‎10-08-2014 03:24 PM
PROC Star
Posts: 7,360

Re: PROC MERGE? or another function?

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;

View solution in original post


All Replies
Super User
Posts: 17,784

Re: PROC MERGE? or another function?

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;

Contributor
Posts: 25

Re: PROC MERGE? or another function?

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

THANK YOU VERY MUCH!! Smiley Happy

Solution
‎10-08-2014 03:24 PM
PROC Star
Posts: 7,360

Re: PROC MERGE? or another function?

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;

Super User
Posts: 17,784

Re: PROC MERGE? or another function?

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 Smiley Happy

Contributor
Posts: 25

Re: PROC MERGE? or another function?

You are right .

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

but your SQL seems to work. Smiley Happy

THANK YOU!!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 286 views
  • 3 likes
  • 3 in conversation