topic How to lag a column based on date difference in SAS Programming

How to lag a column based on date difference

zqkal — Fri, 23 Jun 2017 14:08:03 GMT

Hi SASUSERs,

I needed to lag the column variable based on the number of month different between start and end date. Below is the input data set and desired output.

I appreciate your input.

input dataset:

Type	st_dt	id	ed_dt	amnt
AA	12/1/2016	0	1/1/2017	100.00
AA	1/1/2017	1	1/1/2017	200.00
AA	2/1/2017	2	1/1/2017	300.00
AA	3/1/2017	3	1/1/2017	10.00
BB	12/1/2016	0	2/1/2017	50.00
BB	1/1/2017	1	2/1/2017	105.00
BB	2/1/2017	2	2/1/2017	210.00
BB	3/1/2017	3	2/1/2017	140.00
CC	12/1/2016	0	3/1/2017	130.00
CC	1/1/2017	1	3/1/2017	124.00
CC	2/1/2017	2	3/1/2017	242.00
CC	3/1/2017	3	3/1/2017	298.00

desired output;

Type	st_dt	id	ed_dt	amnt
AA	12/1/2016	0	1/1/2017
AA	1/1/2017	1	1/1/2017	100.00
AA	2/1/2017	2	1/1/2017	200.00
AA	3/1/2017	3	1/1/2017	300.00
BB	12/1/2016	0	2/1/2017
BB	1/1/2017	1	2/1/2017
BB	2/1/2017	2	2/1/2017	50.00
BB	3/1/2017	3	2/1/2017	105.00
CC	12/1/2016	0	3/1/2017
CC	1/1/2017	1	3/1/2017
CC	2/1/2017	2	3/1/2017
CC	3/1/2017	3	3/1/2017	130.00

Here is what I started with but it is not working as I expected it.

%macro xyz();

data t2;

set inputdsn;

by type act_dte;

%do lag_value= 1 %to 70;

if not first.type and first.st_dt then do;

if st_dt ne lag&lag_value(st_dt) then

lag_amnt = lag&lag_value(amnt);

end;

%end;

run;

%mend;

%xyz();

Re: How to lag a column based on date difference

Shmuel — Fri, 02 Jun 2017 21:42:50 GMT

In your code you use different variable names then given with the data:

deal_name act_dte issuance_date are not mentioned with the data.

It is not clear, what are you trying to do and how dates are involved.

What are the rules.

Re: How to lag a column based on date difference

ballardw — Fri, 02 Jun 2017 22:45:00 GMT

Show an example of the code that was working before you attempted the macro.

If you didn't have that, then get some working for a few fixed versions of LAG.

Note that LAG in IF statements is very problematic for success in general.

And you need to expand an your "rule" as what is going is not obvious at all. Like show row by row why the value is selected in the output.

Re: How to lag a column based on date difference

Ksharp — Sat, 03 Jun 2017 04:15:15 GMT

Very interesting.

data have;
infile cards expandtabs truncover;
input Type $	st_dt : $20.	id	ed_dt : $20.	amnt;
cards;
AA	12/1/2016	0	1/1/2017	100.00
AA	1/1/2017	1	1/1/2017	200.00
AA	2/1/2017	2	1/1/2017	300.00
AA	3/1/2017	3	1/1/2017	10.00
BB	12/1/2016	0	2/1/2017	50.00
BB	1/1/2017	1	2/1/2017	105.00
BB	2/1/2017	2	2/1/2017	210.00
BB	3/1/2017	3	2/1/2017	140.00
CC	12/1/2016	0	3/1/2017	130.00
CC	1/1/2017	1	3/1/2017	124.00
CC	2/1/2017	2	3/1/2017	242.00
CC	3/1/2017	3	3/1/2017	298.00
;
run;


data temp;
 set have(keep=type amnt);
 by type;
 n+first.type;
 if first.type then do;
  do i=1 to n;
   _amnt=.;output;
  end;
  _amnt=amnt;output;
 end;
 else do;_amnt=amnt;output;end;
 keep type _amnt;
run;
data want;
 ina=0;
 merge have(in=ina) temp;
 by type;
 if ina;
run;