Hi all,
Not sure how to go about this at all...
I have data like below: DIFF = DUE - ACTV_AMT.
ROW | ID | ACTV_AMT | DATE | DUE | DIFF |
1 | 12 | 350 | 09Jul2020 | 745.38 | 395.38 |
2 | 12 | 300 | 10Aug2020 | 778.7 | 478.7 |
3 | 12 | 300 | 14Aug2020 | 478.7 | 178.7 |
4 | 12 | 178.7 | 09Sep2020 | 571.49 | 392.79 |
5 | 12 | 400 | 11Sep2020 | 392.79 | -7.21 |
I need to add another calculated column to get an output like:
The formula would be: Change = Due(row2) - diff (row 1).
Another complication: For the first row for Change, formula would be: Change = Due - 0.
ROW | ID | ACTV_AMT | DATE | DUE | DIFF | CHANGE |
1 | 12 | 350 | 09Jul2020 | 745.38 | 395.38 | 745.38 |
2 | 12 | 300 | 10Aug2020 | 778.7 | 478.7 | 383.32 |
3 | 12 | 300 | 14Aug2020 | 478.7 | 178.7 | 0 |
4 | 12 | 178.7 | 09Sep2020 | 571.49 | 392.79 | 392.79 |
5 | 12 | 400 | 11Sep2020 | 392.79 | -7.21 | 0 |
Ideally a proc sql solution is needed but I will take anything to complete this ad hoc first... appreciate any direction
I believe this untested data step would work. I would test it if you could provide sample data in the form of a working data step.
The key element here is the lag function:
data want;
set have;
by id;
change=ifn(first.id,due,due-lag(diff));
run;
I presume your data are sorted by id/date. The program above embeds the lag function inside an IFN function. The latter tests whether the record-in-hand is the start of an id - in which case the change variable is assigned a value equal to due. Otherwise due is calculated as you describe.
Ideally you won't even think of using sql for a task that requires observations to be processed in a defined order.
Similar problems are daily posted, so using the search function will show you at least n ways to solve the problem.
Have a look at the documentation of the lag-function.
I believe this untested data step would work. I would test it if you could provide sample data in the form of a working data step.
The key element here is the lag function:
data want;
set have;
by id;
change=ifn(first.id,due,due-lag(diff));
run;
I presume your data are sorted by id/date. The program above embeds the lag function inside an IFN function. The latter tests whether the record-in-hand is the start of an id - in which case the change variable is assigned a value equal to due. Otherwise due is calculated as you describe.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.