BookmarkSubscribeRSS Feed
Nitish1003
Obsidian | Level 7

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

6 REPLIES 6
Astounding
PROC Star

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
Tourmaline | Level 20

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
Obsidian | Level 7

Thanks for the help.Query is working fine

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1077 views
  • 6 likes
  • 4 in conversation