topic Re: Sum cumulative with condition in SAS Programming

Sum cumulative with condition

doudouh — Fri, 17 May 2019 23:52:37 GMT

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

Re: Sum cumulative with condition

PetriRoine — Mon, 20 May 2019 11:21:08 GMT

Hello @doudouh ,

Here is my table per specifications:

... And here is what I did:

Create a new Calculated item.

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

Re: Sum cumulative with condition

doudouh — Mon, 20 May 2019 14:29:12 GMT

Hi Petri Roine,

Thanks for your answer.

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

Thanks

Re: Sum cumulative with condition

PetriRoine — Mon, 27 May 2019 07:46:05 GMT

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

Re: Sum cumulative with condition

doudouh — Tue, 28 May 2019 12:36:39 GMT

Thank you very much.