Cumulative sum

Reply
Occasional Contributor
Posts: 16

Cumulative sum

I am trying to find cumulative sum by year for different makers.

here is my dataset

DATA CUMLILATIVE;
INPUT maker $ MON YEAR sale sale1;
DATALINES;
maruti 1 2015 12 13
maruti 2 2016 13 23
maruti 6 2015 17 18
honda 2 2015 8 9
honda 1 2015 16 19
tata 1 2015 16 19
tata 2 2015 16 19
;
RUN;

 

Output will be like

Maruti 1 2015 12 13

maruti 6 2015 29 31

maruti  2 2016 13 23

honda 1 2015 16 19

honda 2 2015 24  28

and so on

As for maruti we have different year (i.e. 2015 and 2016) the cumulative sum must be year wise.

Please help 

Thanks in advance

Respected Advisor
Posts: 4,993

Re: Cumulative sum

First sort your data:

 

proc sort data=have;

by maker year mon;

run;

 

Note that MAKER must take on the same spelling.  "Maruti" is different than "maruti".

 

Then accumulate:

 

data want;

set have;

by maker year;

if first.year then do;

   tot_sales = sale;

   tot_sales1 = sale1;

end;

else do;

   tot_sales + sale;

   tot_sales1 + sale1;

end;

drop sale sale1;

run;

 

Trusted Advisor
Posts: 1,513

Re: Cumulative sum

[ Edited ]

Same but slightly more compact:

 

data WANT;
  set HAVE;
  by MAKER YEAR;
  if first.YEAR then call missing(TOT_SALES,TOT_SALES1);
  TOT_SALES  + SALE;
  TOT_SALES1 + SALE1;
  drop SALE SALE1;
run; 

 

Occasional Contributor
Posts: 16

Re: Cumulative sum

Thanks for the help both code are working properly.

need one more help can you please guide me how can i get same output through proc sqll

 

Thanks

Respected Advisor
Posts: 4,993

Re: Cumulative sum

My SQL is relatively weak, so this falls into the category of "worth a try":

 

proc sql;

create table want as 

select a.maker, a.year, a.mon. sum(b.sales) as totsales, sum(b.sales1) as totsales1

from have a, have b

where (b.year < a.year) or (b.year = a.year and b.mon <= a.mon)

group by a.maker, a.year, a.mon;

quit;

 

In general, SQL does not guarantee an order to the observations.  So problems that depend on the order become more difficult.

Contributor
Posts: 24

Re: Cumulative sum

Actually @Astounding got very close.

 

proc sql;
     create table want as
     select a.maker, a.year, a.mon, sum(b.sale) as sales, sum(b.sale1) as sales1
     from have a, have b
     where a.maker = b.maker and b.year = a.year and b.mon <= a.mon
     group by a.maker, a.year, a.mon;
quit;

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

Occasional Contributor
Posts: 16

Re: Cumulative sum

Thanks for the help.Query is working fine

Ask a Question
Discussion stats
  • 6 replies
  • 188 views
  • 6 likes
  • 4 in conversation