Hello Everyone , I have this Dataset :
Index | Diff_Day | Price_Month | Price_Day |
L1 | -2 | 12,3 | 45 |
L1 | -1 | 1234 | 102 |
L1 | 0 | 154 | 745 |
L1 | 1 | 654 | 632 |
L1 | 2 | 54 | 12 |
L2 | -2 | 554 | 302 |
L2 | -1 | 35 | 123 |
L2 | 0 | 543 | 1023 |
L2 | 1 | 545 | 3012 |
L2 | 2 | 421 | 56 |
L3 | -2 | 213 | 878 |
L3 | -1 | 3253 | 353 |
L3 | 0 | 354 | 213 |
L3 | 1 | 154 | 3213 |
L3 | 2 | 1230 | 3021 |
And i want to do a cumulative sum for price month and price day , but starting from diff_day 0 , like my output would be like this (here , i'm showing the cumulative sum for price month , but i want to do the same thing for price day):
Index | Diff_Day | Price_Month | Price_Day | CumSum_Price_Month | |
L1 | -2 | 12,3 | 45 | 12,3 | |
L1 | -1 | 1234 | 102 | 1234 | |
L1 | 0 | 154 | 745 | 154 | |
L1 | 1 | 654 | 632 | 808 | |
L1 | 2 | 54 | 12 | 862 | |
L2 | -2 | 554 | 302 | 554 | |
L2 | -1 | 35 | 123 | 35 | |
L2 | 0 | 543 | 1023 | 543 | |
L2 | 1 | 545 | 3012 | 1088 | |
L2 | 2 | 421 | 56 | 1509 | |
L3 | -2 | 213 | 878 | 213 | |
L3 | -1 | 3253 | 353 | 3253 | |
L3 | 0 | 354 | 213 | 354 | |
L3 | 1 | 154 | 3213 | 508 | |
L3 | 2 | 1230 | 3021 | 1738 |
Any suggestion on how to do that would much appreciated , thank you.
data have;
input Index $ Diff_Day Price_Month Price_Day;
datalines;
L1 -2 12.3 45
L1 -1 1234 102
L1 0 154 745
L1 1 654 632
L1 2 54 12
L2 -2 554 302
L2 -1 35 123
L2 0 543 1023
L2 1 545 3012
L2 2 421 56
L3 -2 213 878
L3 -1 3253 353
L3 0 354 213
L3 1 154 3213
L3 2 1230 3021
;
data want;
do until (last.Index);
set have;
by Index;
if Diff_Day <= 0 then do;
CumSum_Price_Month = Price_Month;
CumSum_Price_Day = Price_Day;
end;
else do;
CumSum_Price_Month + Price_Month;
CumSum_Price_Day + Price_Day;
end;
output;
end;
run;
Result:
Index Diff_Day Price_Month Price_Day CumSum_Price_Month CumSum_Price_Day L1 -2 12.3 45 12.3 45 L1 -1 1234.0 102 1234.0 102 L1 0 154.0 745 154.0 745 L1 1 654.0 632 808.0 1377 L1 2 54.0 12 862.0 1389 L2 -2 554.0 302 554.0 302 L2 -1 35.0 123 35.0 123 L2 0 543.0 1023 543.0 1023 L2 1 545.0 3012 1088.0 4035 L2 2 421.0 56 1509.0 4091 L3 -2 213.0 878 213.0 878 L3 -1 3253.0 353 3253.0 353 L3 0 354.0 213 354.0 213 L3 1 154.0 3213 508.0 3426 L3 2 1230.0 3021 1738.0 6447
data have;
input Index $ Diff_Day Price_Month Price_Day;
cards;
L1 -2 12.3 45
L1 -1 1234 102
L1 0 154 745
L1 1 654 632
L1 2 54 12
L2 -2 554 302
L2 -1 35 123
L2 0 543 1023
L2 1 545 3012
L2 2 421 56
L3 -2 213 878
L3 -1 3253 353
L3 0 354 213
L3 1 154 3213
L3 2 1230 3021
;
data want;
set have;
by index;
if first.index or Diff_Day<=0 then do;
CumSum_Price_Month=Price_Month;
cumPrice_day=Price_Day;
return;
end;
CumSum_Price_Month+Price_Month;
cumPrice_day+Price_Day;
run;
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.