topic Re: merge two dataset by id and date in SAS Programming

merge two dataset by id and date

wsydy — Sat, 03 Nov 2018 19:48:16 GMT

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

Re: merge two dataset by id and date

VDD — Sat, 03 Nov 2018 19:52:13 GMT

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#M9151

Re: merge two dataset by id and date

Reeza — Sat, 03 Nov 2018 21:47:03 GMT

The documentation should be the first place you check.

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p15jvywi5avt3cn1bee8r6c33ux1.htm&docsetVersion=9.4&locale=en#n0jx3gi7uxxgr1n1q9yklnxpxbma

@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

Re: merge two dataset by id and date

Astounding — Sat, 03 Nov 2018 23:57:42 GMT

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;

Re: merge two dataset by id and date

wsydy — Sun, 04 Nov 2018 16:47:11 GMT

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