topic Re: How do I retain a value within a group and calculate in current row? in SAS Programming

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

icatNYC — Thu, 16 Jan 2020 17:32:16 GMT

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/m-p/591676#M15243

and

https://communities.sas.com/t5/SAS-Programming/How-to-retain-a-value-from-the-previous-row-to-do-calculations/m-p/370765#M88549

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

bests

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

Reeza — Thu, 16 Jan 2020 17:41:38 GMT

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

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

Reeza — Thu, 16 Jan 2020 17:55:58 GMT

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;

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

ErikLund_Jensen — Thu, 16 Jan 2020 18:01:58 GMT

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:

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

icatNYC — Thu, 16 Jan 2020 19:23:47 GMT

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

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

icatNYC — Thu, 16 Jan 2020 19:32:17 GMT

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.

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

icatNYC — Thu, 16 Jan 2020 19:35:28 GMT

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.

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

icatNYC — Thu, 16 Jan 2020 21:08:59 GMT

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

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

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

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