How do I retain a value within a group and calculate in current row?

[icatNYC]

Hello SAS Experts,

I am following up with a problem that I am stuck at. I want to calculate 2 variables - a) one depends on a previous record and b) depends on a). Here is an illustrative example:

/*
If Index = 1 then Open=0
else Open=Prev_Close

Leeway=Limit-Close
*/

Data Sample Have;
input ID Index Order Limit;
datalines;
1 1 5 50
1 2 12 50
1 3 45 50
2 1 10 100
2 2 30 100
2 3 15 100
;
run;

Data Sample_Evaluation Want;
input ID Index Order Limit Open Close Leeway;
datalines;
1 1 5 50 0 5 45
1 2 12 50 5 17 33
1 3 45 50 17 62 -12
2 1 10 100 0 10 90
2 2 30 100 10 40 60
2 3 15 100 40 55 45
;
run;

proc print data=Sample_Evaluation; run;

I have looked at the following posts:

https://communities.sas.com/t5/New-SAS-User/How-do-I-retain-a-value-within-a-group-based-on-several/...

and

https://communities.sas.com/t5/SAS-Programming/How-to-retain-a-value-from-the-previous-row-to-do-cal...

but have not been successful at my attempts to generate desired results yet. Please advise. Any help would be greatly appreciated.

bests

IC

[Reeza]

Please show what you've tried as well. Any particular reason you're creating two copies of each data set?

[icatNYC]

I kept 2 datasets as it seems to be the posting convention to use Have/Want pairs. Here is what I have tried:

Option compress=yes;

Data Sample Have;
input ID Index Order Limit;
datalines;
1 1 5 50
1 2 12 50
1 3 45 50
2 1 10 100
2 2 30 100
2 3 15 100
;
run;

Data Sample_Evaluation;
retain Close;
set Sample;
if index=1 then do;
Open=0;
Close=Open+Order;
Leeway=Limit-Close;
end;

if index ge 1 then do;
Open=Close;
Close=Open+Order; 
Leeway=Limit-Close;
end;
run;

Data Sample_Evaluation;
retain ID Index Order Limit Open Close Leeway;
set Sample_Evaluation;
run;


Data Want;
input ID Index Order Limit Open Close Leeway;
datalines;
1 1 5 50 0 5 45
1 2 12 50 5 17 33
1 3 45 50 17 62 -12
2 1 10 100 0 10 90
2 2 30 100 10 40 60
2 3 15 100 40 55 45
;
run;

proc print data=Sample_Evaluation; run;

The print output is: 

The SAS System

Obs ID Index Order Limit Open Close Leeway123456

1	1	5	50	5	10	40
1	2	12	50	10	22	28
1	3	45	50	22	67	-17
2	1	10	100	10	20	80
2	2	30	100	20	50	50
2	3	15	100	50	65	35

[Reeza · Accepted solution]

data want;
	set have;
	by ID;
	retain open close leeway;

	if first.id then
		do;
			open=0;
			close=order;
			leeway=limit-order;
		end;
	else
		do;
			open=close;
			close=open + order;
			leeway=limit - close;
		end;
run;

[icatNYC]

Thank you. I think this works for my sample but please give me a moment to verify on the larger complex condition that I am working on. I will confirm and post when done.

[icatNYC]

Thank you. The code works as intended. I am able to follow your steps.

[ErikLund_Jensen]

Hi @icatNYC 

T think there is something missing in your algoritm, as you don't specify how close should be computed. Based on your output I came up with the following (using your input):

data want (drop=oOrder); set have;
 	by ID;
	length Open Close Leeway 8;
	retain Open Close;
	oOrder = lag(Order);
	if first.ID then do;
		Open = 0;
		Close = Order;
	end;
	else do;
		Close = Close + Order;
		Open = Open + oOrder;
	end;
	leeway = Limit-Close;
run;

Result:

[icatNYC]

Yes, I did not post my attempt at the code earlier which has the logic to calculate close. I posted it as a response to Reeza's request but I should have been mindful earlier. my apologies! 

You have correctly guessed the intended logic for close though - I am evaluating your response.