topic Re: Cumulative Sum by 2 Groups in SAS Programming

Cumulative Sum by 2 Groups

Nasser_DRMCP — Tue, 17 Apr 2018 08:38:30 GMT

data cars ;
set sashelp.cars (keep=Make Type Invoice
where=(Make in ('Buick' 'SUV'))
				);
run ;

Hello,

for example in this "cars" dataset, I would like to get a column "invoice_cumul" that cumul invoice by make, type

thanks in advance for your help

Nasser

Re: Cumulative Sum by 2 Groups

PeterClemmensen — Tue, 17 Apr 2018 08:45:07 GMT

Are you sure this is the data set you want to work with? Make is never equal to "SUV"?

Re: Cumulative Sum by 2 Groups

Nasser_DRMCP — Tue, 17 Apr 2018 08:50:00 GMT

hello,

sorry for that. this is better

data cars ;
set sashelp.cars (keep=Make Type Invoice
where=(Make in ('Buick' 'Cadillac'))
				);
run ;

Re: Cumulative Sum by 2 Groups

RW9 — Tue, 17 Apr 2018 08:54:52 GMT

There are many examples of this in the SAS docs and around. For example proc means/summary, sum() in proc sql, retain a sum variable in datastep etc. It really is too simple a question to provide actual code for, just look at the docs for proc means/summary with a by group.

Re: Cumulative Sum by 2 Groups

Nasser_DRMCP — Tue, 17 Apr 2018 09:55:20 GMT

hello RW9,

thanks for your respons. but whenever a post a question , it is because i did not found the solve my problem by taking a look in the docs. Here is my dataset "have". I would like to get the amout_cumul for each product/month_amo group , for example the first cumul would be 11 + 12 +13 , the second 21+22+23...

Data have  ;
infile datalines ;
input product ~ $4. month_amo month_prod amount ;
datalines ;
PRD1 201801 201801 11
PRD1 201801 201802 12
PRD1 201801 201803 13	
PRD1 201802 201801 21
PRD1 201802 201802 22
PRD1 201802 201803 23
PRD1 201803 201801 31
PRD1 201803 201802 32
PRD1 201803 201803 33
PRD2 201801 201801 41
PRD2 201801 201802 42	
PRD2 201801 201803 43	
PRD2 201802 201801 51
PRD2 201802 201802 52
PRD2 201802 201803 53
PRD2 201803 201801 61
PRD2 201803 201802 62
PRD2 201803 201803 63
;

Re: Cumulative Sum by 2 Groups

Nasser_DRMCP — Tue, 17 Apr 2018 10:07:53 GMT

I found the solution by doing that

proc sort data=have ; by product month_amo month_prod ; run ;

data want  ;
set have ;
by product month_amo ;
retain  amount_cumul ;
if  first.month_amo then amount_cumul = amount ;
else  amount_cumul + amount ;
run ;

Re: Cumulative Sum by 2 Groups

RW9 — Tue, 17 Apr 2018 10:59:09 GMT

From search;

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&filter=location&location=forum-board%3Aprogramming&q=cumulative+sum

50 responses all with code which does what you want.