DATA Step, Macro, Functions and more

Cumulative Sum by 2 Groups

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

Cumulative Sum by 2 Groups

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


Accepted Solutions
Solution
‎04-17-2018 06:12 AM
Frequent Contributor
Posts: 117

Re: Cumulative Sum by 2 Groups

Posted in reply to Nasser_alfea

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 ;

View solution in original post


All Replies
PROC Star
Posts: 1,283

Re: Cumulative Sum by 2 Groups

Posted in reply to Nasser_alfea

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

Frequent Contributor
Posts: 117

Re: Cumulative Sum by 2 Groups

hello,

sorry for that. this is better

data cars ;
set sashelp.cars (keep=Make Type Invoice
where=(Make in ('Buick' 'Cadillac'))
				);
run ;
Super User
Super User
Posts: 9,599

Re: Cumulative Sum by 2 Groups

Posted in reply to Nasser_alfea

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.

Frequent Contributor
Posts: 117

Re: Cumulative Sum by 2 Groups

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
;
Solution
‎04-17-2018 06:12 AM
Frequent Contributor
Posts: 117

Re: Cumulative Sum by 2 Groups

Posted in reply to Nasser_alfea

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 ;
Super User
Super User
Posts: 9,599

Re: Cumulative Sum by 2 Groups

Posted in reply to Nasser_alfea

From search;

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&...

 

50 responses all with code which does what you want. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 195 views
  • 0 likes
  • 3 in conversation