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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.