Hi,
I have got two datasets as D1 and D2
D1
id2 | id1 | open_date | amt |
122234 | 123567 | 01-Apr-09 | 5 |
122234 | 123567 | 10-Apr-09 | 4 |
122234 | 123567 | 01-May-09 | 2 |
122234 | 123567 | 01-Jun-10 | 5 |
122234 | 123567 | 01-Jul-10 | 1 |
122234 | 123567 | 01-Sep-10 | 2 |
122234 | 123567 | 01-Oct-10 | 6 |
122234 | 123567 | 03-Dec-12 | 4 |
134221 | 123568 | 02-Sep-11 | 6 |
134222 | 123568 | 02-Apr-09 | 5 |
134222 | 123568 | 12-Apr-09 | 4 |
134222 | 123568 | 11-May-09 | 2 |
134222 | 123568 | 12-Jun-10 | 5 |
134222 | 123568 | 02-Jul-10 | 1
|
D2
id2 | open_date | amt |
122234 | 02-Mar-09 | 6 |
122234 | 11-Apr-09 | 7 |
122234 | 02-May-09 | 4 |
122234 | 05-Jun-09 | 1 |
122234 | 02-Jul-10 | 5 |
122234 | 05-Sep-10 | 3 |
122234 | 04-Nov-10 | 8 |
134221 | 11-Sep-11 | 2 |
134222 | 10-May-09 | 5 |
134222 | 25-May-09 | 3 |
134222 | 26-May-09 | 5 |
134222 | 28-May-09 | 7 |
Now the task is :
I need to take all those amounts from D2 where any date per account from d1 > (all dates from d2) The two datasets can be linked on D2.
all I am trying to create a macro which will compare the d1 and d2 on acc numbers and check the dates from d1 into d2 and whereever date from d1 is greater than d2 get the amount of the prior date
This is not a job for a macro.
The solution can be done via merging the two data sets in a data step.
But before we provide a solution, show us the output data set you want from this.
The output dataset should be something like below
id1 | open_date | amt_d1 | amt_d2 |
123567 | 01-Apr-09 | 5 | 6 |
123567 | 10-Apr-09 | 4 | 7 |
123567 | 01-May-09 | 2 | 4 |
123567 | 01-Jun-10 | 5 | 1 |
123567 | 01-Jul-10 | 1 | 5 |
123567 | 01-Sep-10 | 2 | 3 |
123567 | 01-Oct-10 | 6 | 8 |
123567 | 03-Dec-12 | 4 | 2 |
123568 | 02-Sep-11 | 6 | 5 |
123568 | 02-Apr-09 | 5 | 3 |
123568 | 12-Apr-09 | 4 | 5 |
123568 | 11-May-09 | 2 | 7 |
so amt is the last amt prior to each date as you loop through the dates in between d1 and d2
so if we loop through open date in d1 then the dtes will be like 01-apr,02-apr,03-apr,04-apr,05-apr,06-apr and so on and these dates we need to compare with the open date from d2 so if the date say 01apr2009 from d1 falls between the dates 02ma2009 till 11apr2009 then return the amt at 02mar2009,if its not falling between those dates then check the next two dates (make sure the open dte in d2 is sorted in ascending order)
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: