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
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;
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
The documentation should be the first place you check.
@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
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;
Thanks very much for your help!! It works!!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.