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();
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.
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.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.