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
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;
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;
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
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.
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
Thanks for the help.Query is working fine
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.