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;

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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