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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.