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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: