Struggling to create two fields dependent on each other

[CStanyer76]

I need to create two columns in a table, but the columns are dependant on each other. But there is no circular reference.  The table I need to create is similar to the example below.  The fields Account, Month, Original Balance and Perc already exist.  I need to calculate Cost and Running Balance.

The table is sorted by account and month.  For the first instance of an account appearing Cost is calculated as Original Balance * Perc and Running Bal is Original Balance-Cost.

In the subsequent rows Cost is the previous months Running Bal*Perc and the new Running Bal is the previous months Running Bal - this months Cost.

I've been able to create either the first row or the second row but not all rows for all accounts.

Account	Month	Original Balance	Perc	Cost	Running Bal
1234	JAN	1000	1.0%	10	990.00
1234	FEB	1000	1.2%	11.9	978.12
1234	MAR	1000	1.1%	10.8	967.36
1234	APR	1000	1.3%	12.6	954.78
1234	MAY	1000	1.0%	9.5	945.24
1234	JUN	1000	0.9%	8.5	936.73
4561	JAN	1200	0.9%	10.68	1189.32
4561	FEB	1200	0.8%	9.8	1179.57
4561	MAR	1200	0.8%	8.8	1170.72
4561	APR	1200	0.7%	8.0	1162.76
4561	MAY	1200	0.6%	7.1	1155.67
4561	JUN	1200	0.5%	6.2	1149.43

[Satish_Parida]

data have;
input Account $	Month $	Original_Balance Perc;
cards;
1234 JAN 1000 1.0
1234 FEB 1000 1.2
1234 MAR 1000 1.1
1234 APR 1000 1.3
1234 MAY 1000 1.0
1234 JUN 1000 0.9
4561 JAN 1200 0.9
4561 FEB 1200 0.8
4561 MAR 1200 0.8
4561 APR 1200 0.7
4561 MAY 1200 0.6
4561 JUN 1200 0.5
;
run;

data want(drop=current);
length current 8.;
set have;
by Account;
retain current;
if first.Account then do;
	Cost=round(Original_Balance*Perc/100,.01);
	Running_Bal=Original_Balance-Cost;
	current=Running_Bal;
end;
else do;
	Cost=round(current*Perc/100,.01);
	Running_Bal=current-Cost;
	current=Running_Bal;
end;
run;

Suggestion : Use months as numbers, which will be easy to sort.

Please let us know if this worked for you.

@Experts, Can we do it using Lag Function?

[CStanyer76]

This works, thank you!

[ed_sas_member]

You're welcome 

[ed_sas_member · Accepted solution]

Hi @CStanyer76 

Here is another method.

In my opinion, you should not round values during the calculation process, but rather use a format for display.

data have;
	infile datalines dlm="09"x;
	input Account Month $ Original_Balance Perc:percent3.1;
	format Perc percent8.1;
	datalines;
1234	JAN	1000	1.0%
1234	FEB	1000	1.2%
1234	MAR	1000	1.1%
1234	APR	1000	1.3%
1234	MAY	1000	1.0%
1234	JUN	1000	0.9%
4561	JAN	1200	0.9%
4561	FEB	1200	0.8%
4561	MAR	1200	0.8%
4561	APR	1200	0.7%
4561	MAY	1200	0.6%
4561	JUN	1200	0.5%
;
run;

data want;
	set have;
	
	by Account Month notsorted;
	
	format Cost Running_Bal 10.2;
	
	retain Cost;
	retain Running_Bal;
	
	if first.Account then do;
		Cost = Original_Balance * Perc;
		Running_Bal = Original_Balance-Cost;
	end;
	
	else do;
		Cost = Running_Bal * Perc;
		Running_Bal = Running_Bal - Cost;
	end;

run;