topic Re: how to create a function in a data step using a dataset in SAS Programming

how to create a function in a data step using a dataset

MV04 — Tue, 09 Apr 2019 19:21:25 GMT

Hi,

I have got two datasets as D1 and D2

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

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

Re: how to create a function in a data step using a dataset

PaigeMiller — Tue, 09 Apr 2019 19:48:09 GMT

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.

Re: how to create a function in a data step using a dataset

MV04 — Tue, 09 Apr 2019 22:24:34 GMT

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)