Cumulative sum

[Nitish1003]

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

[Astounding]

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;

[ChrisNZ]

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; 

[Nitish1003]

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

[Astounding]

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.

[Daniel-Santos]

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

[Nitish1003]

Thanks for the help.Query is working fine