topic Cumulative sum in SAS Programming

Cumulative sum

Nitish1003 — Mon, 09 Jan 2017 19:01:31 GMT

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

Re: Cumulative sum

Astounding — Mon, 09 Jan 2017 19:18:59 GMT

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;

Re: Cumulative sum

ChrisNZ — Mon, 09 Jan 2017 22:51:31 GMT

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;

Re: Cumulative sum

Nitish1003 — Tue, 10 Jan 2017 06:27:09 GMT

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

Re: Cumulative sum

Astounding — Tue, 10 Jan 2017 08:06:52 GMT

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.

Re: Cumulative sum

Daniel-Santos — Tue, 10 Jan 2017 08:21:25 GMT

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

Re: Cumulative sum

Nitish1003 — Tue, 10 Jan 2017 20:34:29 GMT

Thanks for the help.Query is working fine