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:
and
but have not been successful at my attempts to generate desired results yet. Please advise. Any help would be greatly appreciated.
bests
IC
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;
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 |
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 |
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;
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.
Thank you. The code works as intended. I am able to follow your steps.
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:
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.
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.