topic Re: How to create a table in sas which has cumulative formulas used to derive the observations? in SAS Programming

How to create a table in sas which has cumulative formulas used to derive the observations?

Shradha1 — Mon, 24 May 2021 14:26:06 GMT

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:

the first observation for amount_at_start is known to us, but thereafter next row onwards, amount_at_start=amount_at_end from previous row
spend and savings figures for each date is already given to us.
amount_at_end= (amount_at_start +spend _savings)
charge_percentage is known to us
charge_amount= (charge_percentage/100)*1/365*amount_at_end

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!

Re: How to create a table in sas which has cumulative formulas used to derive the observations?

s_lassen — Tue, 25 May 2021 10:12:24 GMT

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;

Re: How to create a table in sas which has cumulative formulas used to derive the observations?

PhilC — Wed, 02 Jun 2021 18:50:25 GMT

The retain statement is useful.

SAS Help Center: RETAIN Statement

An example

SAS Help Center: Manipulate Variables