I have a table in excel and I need to create a similar table in sas.
The table is like this:
DATE | amount_at_start | Spend | savings | amount_at_end | charge_percentage | charge_amount |
17-Apr-21 | 100.00 | 10.00 | 0.00 | 110.00 | 10.00 | 0.03 |
18-Apr-21 | 110.00 | 0.00 | 0.00 | 110.00 | 10.00 | 0.03 |
19-Apr-21 | 110.00 | 0.00 | 0.00 | 110.00 | 10.00 | 0.03 |
20-Apr-21 | 110.00 | 0.00 | 0.00 | 110.00 | 10.00 | 0.03 |
21-Apr-21 | 110.00 | 20.00 | 40.00 | 90.00 | 10.00 | 0.02 |
22-Apr-21 | 90.00 | 0.00 | 0.00 | 90.00 | 10.00 | 0.02 |
23-Apr-21 | 90.00 | 0.00 | 0.00 | 90.00 | 10.00 | 0.02 |
The formulas used in the table are:
This calculation is happening date wise row by row.
So if we have the first entry of amount_at_start, all the entries of spends and savings, and the charge_percentage given to us, how do we create a similar table in SAS
Any help on this would be much appreciated. Thanks!
You have much better chances of getting an answer if you supply the input data (and maybe also the output you want) in a datastep, like this:
data have;
input date date9. amount_at_start Spend savings charge_percentage;
format date date9.;
cards;
17-Apr-21 100.00 10.00 0.00 10.00
18-Apr-21 . 0.00 0.00 10.00
19-Apr-21 . 0.00 0.00 10.00
20-Apr-21 . 0.00 0.00 10.00
21-Apr-21 . 20.00 40.00 10.00
22-Apr-21 . 0.00 0.00 10.00
23-Apr-21 . 0.00 0.00 10.00
run;
I think you can get the data you want like this:
data want;
set have;
retain amount_at_end 0;
if missing(amount_at_start) then amount_at_start=amount_at_end;
amount_at_end=amount_at_start+spend-savings;
charge_amount= round((charge_percentage/100)*1/365*amount_at_end,0.01);
run;
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.