BookmarkSubscribeRSS Feed
Midi
Obsidian | Level 7

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.

2 REPLIES 2
PeterClemmensen
Tourmaline | Level 20
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 
novinosrin
Tourmaline | Level 20


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;
How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 817 views
  • 0 likes
  • 3 in conversation