Sum cumulative with condition

[doudouh]

Dear all, can someone help me how to do in order to have the table want from the table have?
I want to sum from first obs to 3th, from the second to 4th,.......

Have:
Date number
062018 10
052018 15
042018 20
032018 15
022018 30
012018 10

Want:

Date number
062018 45 (10+15+20)
052018 50 (15+20+15)
042018 65 (20+15+30)
032018 55 (15+30+10)
022018 40 (30+10)
012018 10 (10)

Thanks for yours help.
Ad

[PetriRoine]

Hello @doudouh ,

Here is my table per specifications:

... And here is what I did:

1.  Create a new Calculated item.
2. Let's use new aggregate operator called Relative Period and define it to calculate simple sum of current month (0), previous month (-1), and a month before that (-2). Here is the code snippet that you can copy to Text Editor:
   
   
   

   (
   IF ( RelativePeriod(_Sum_, 'number'n,
   _IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, 0, _Full_,
   {Date}) NotMissing )
   RETURN RelativePeriod(_Sum_, 'number'n,
   _IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, 0, _Full_,
   {Date})
   ELSE 0 ) + 
   (IF ( RelativePeriod(_Sum_, 'number'n,
   _IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, -1, _Full_,
   {Date}) NotMissing )
   RETURN RelativePeriod(_Sum_, 'number'n,
   _IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, -1, _Full_,
   {Date})
   ELSE 0 ) + 
   (IF ( RelativePeriod(_Sum_, 'number'n,
   _IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, -2, _Full_,
   {Date}) NotMissing )
   RETURN RelativePeriod(_Sum_, 'number'n,
   _IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, -2, _Full_,
   {Date})
   ELSE 0 )

    

Let me know if you need more detailed helped.

Best regards,
Petri Roine

[doudouh]

Hi Petri Roine,

Thanks for your answer.

I forgot to specify that I want this manipulation  in SAS Base (not in SAS VA).

Thanks

Ad

[PetriRoine]

Apologies. I was probably working with VA at that time and just used it without thinking too much. Here's a code snippet to solve your problem in BASE SAS. I'm using SAS Lag function which makes solving this very straightforward.

/* Sort to proper order. My example file was in wrong order */
proc sort data=sum_cumulative;
     by date;
run;

/* Input file: sum_cumulative. Output file: newdata */
data newdata;
	set sum_cumulative;
	number = number + lag1(number) + lag2(number);
run;

I hope this helps!

Best regards,

Petri

[doudouh]

Thank you very much.