merge two dataset by id and date

[wsydy]

dataset 1

ID	Date
1	01/13/2017
1	01/14/2017
1	01/15/2017
1	02/15/2018
2	01/13/2017
2	01/15/2017
2	02/15/2018
3	01/13/2017
3	01/15/2017
3	02/15/2018

dataset 2

ID	level	Date
1	A	01/01/1993
1	B	01/14/2017
1	C	12/17/2017
2	A	05/24/2017
3	A	11/11/2016

Want

ID	Date	Level
1	01/13/2017	A
1	01/14/2017	B
1	01/15/2017	B
1	02/15/2018	C
2	01/13/2017
2	01/15/2017
2	02/15/2018	A
3	01/13/2017	A
3	01/15/2017	A
3	02/15/2018	A

Hi there.

I want to merge the dataset1 and dataset2 by id and date. For each ID, I want to add variable 'level' to dataset1 based on its last record (in dataset2) before the given date in dataset1.

For example, for a given date 01/13/2017 in dateset1. I want the level variable to be 'A', since according to dataset2, the last record before 01/13/2017 is 'A' ( on 01/01/1993).

Any help to achieve this in sas?

Thanks

[VDD]

Here is a link that explains how to merge.

https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-in-sas/

Another user has a question much like the one you have

 https://communities.sas.com/t5/SAS-Data-Management/Merging-two-datasets-by-ID-and-date/m-p/318404#M9...

[Reeza]

The documentation should be the first place you check. 

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p15jvywi5avt3cn1bee8r6c33ux1.htm&docsetVe...

---

@wsydy wrote:

dataset 1

ID	Date
1	01/13/2017
1	01/14/2017
1	01/15/2017
1	02/15/2018
2	01/13/2017
2	01/15/2017
2	02/15/2018
3	01/13/2017
3	01/15/2017
3	02/15/2018

dataset 2

ID	level	Date
1	A	01/01/1993
1	B	01/14/2017
1	C	12/17/2017
2	A	05/24/2017
3	A	11/11/2016

Want

ID	Date	Level
1	01/13/2017	A
1	01/14/2017	B
1	01/15/2017	B
1	02/15/2018	C
2	01/13/2017
2	01/15/2017
2	02/15/2018	A
3	01/13/2017	A
3	01/15/2017	A
3	02/15/2018	A

Hi there.

I want to merge the dataset1 and dataset2 by id and date. For each ID, I want to add variable 'level' to dataset1 based on its last record (in dataset2) before the given date in dataset1.

For example, for a given date 01/13/2017 in dateset1. I want the level variable to be 'A', since according to dataset2, the last record before 01/13/2017 is 'A' ( on 01/01/1993).

Any help to achieve this in sas?

Thanks

---

[Astounding · Accepted solution]

Actually, what you are  trying to do is not a standard merge, and I doubt that reading the documentation will help.  It's mildly complex.  Assuming that both data sets are sorted by ID and DATE:

data want;

set  dataset2 (in=in2 rename=(level=fetch_this_level)) dataset1 (in=in1);

by id date;

length level $ 1;    /* might need to be longer ... in real life it might not be just "A" "B" "C" */

retain level;

if first.ID then level = ' ';

if in2 then level = fetch_this_level;

if in1;

drop fetch_this_level;

run;

[wsydy]

Thanks very much for your help!! It works!!