DATA Step, Macro, Functions and more

How to lag a column based on date difference

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

How to lag a column based on date difference

[ Edited ]

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();


Accepted Solutions
Solution
‎06-23-2017 10:06 AM
Super User
Posts: 10,500

Re: How to lag a column based on date difference

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,374

Re: How to lag a column based on date difference

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. 

 

 

 

 

 

Solution
‎06-23-2017 10:06 AM
Super User
Posts: 10,500

Re: How to lag a column based on date difference

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.

Super User
Posts: 9,681

Re: How to lag a column based on date difference

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 113 views
  • 0 likes
  • 4 in conversation