topic Re: Summing up values for same year in SAS Programming

Summing up values for same year

devsas — Thu, 23 Feb 2017 20:42:39 GMT

Good afternoon everyone! As always, I thought its pretty simple but got stuck. Here is the data-

Data have;

input code $ description $ totalSum totalUnits year;

cards;

A APPLE 100 3 2016

A APPLE 200 4 2015

A APPLE 300 5 2014

B BANANA 234 7 2015

C CIDER 20 1 2016

C CIDER 25 3 2016

C CIDER 12 1 2015

C CIDER 21 4 2015

C CIDER 23 4 2014

D DATE 200 4 2015

D DATE 567 34 2016

;

RUN;

What I'm looking for is that for those records within the same code, description and year, totalsum and totalunits values are added. For ex, if there is only one unique value for C, Cider and 2014, it should remain the same, but when C, Cider and 2014 are repeated again, totalsum values should be added to give one unique record for that year. To make it more clear-here is what I want-

data want;

input code $ description $ totalSum totalUnits year;

cards;

A APPLE 100 3 2016

A APPLE 200 4 2015

A APPLE 300 5 2014

B BANANA 234 7 2015

C CIDER 45 4 2016

C CIDER 33 5 2015

C CIDER 23 4 2014

D DATE 200 4 2015

D DATE 567 34 2016

;

RUN;

Thanks so much!

Re: Summing up values for same year

art297 — Thu, 23 Feb 2017 20:49:51 GMT

proc summary data=have nway;
  by code description year notsorted;
  var totalSum totalUnits;
  output out=want (drop=_:) sum=;
run;

Art, CEO, AnalystFinder.com

Re: Summing up values for same year

Shmuel — Thu, 23 Feb 2017 20:55:25 GMT

proc summary data=have missing nway;

class code description year;

var totalSum totalUnits;

output out=want sum=;

run;

you can also use proc means instead proc summary.

Re: Summing up values for same year

Yavuz — Thu, 23 Feb 2017 21:14:34 GMT

I didnt tried but maybe give alternative idea,

data want;
set have;
by code description year;
cumtotsum + (-first.year * cumtotsum) + totalsum;
run;

Re: Summing up values for same year

devsas — Thu, 23 Feb 2017 21:18:18 GMT

It still gives me multiple code, description and year values.

Re: Summing up values for same year

art297 — Thu, 23 Feb 2017 21:21:10 GMT

If you are getting multiple values, it is because your data isn't sorted as shown in your example.

Use @Shmuel's code which does the same thing, but uses a class rather than a by statement. That way your data doesn't have to be presorted.

Art, CEO, AnalystFinder.com

Re: Summing up values for same year

devsas — Thu, 23 Feb 2017 21:27:46 GMT

Thanks sir, tried yours as well with my original data and the same result, my year is in form of d_2014 d_2015 and d_2016..hope that is not affecting results. Also, some descriptions are blank.

Re: Summing up values for same year

art297 — Thu, 23 Feb 2017 22:00:11 GMT

Not sure what you data actually look like but, from what I can tell, your description field may be the culpret. Try:

proc summary data=have nway;
  class code year;
  var totalSum totalUnits;
  output out=want (drop=_:) sum=;
run;

Art, CEO, AnalystFinder.com

Re: Summing up values for same year

devsas — Thu, 23 Feb 2017 22:09:06 GMT

Thanks again! Yes, that might be a problem. As I mentioned earlier, some of the description values are blank. I'm out now, but will try again tomorrow morning and will let you know if this works.

Re: Summing up values for same year

devsas — Fri, 24 Feb 2017 14:33:37 GMT

This seems to have worked. I guess I have to now merge this with original dataset on codes to get descriptions. Thanks again.

Re: Summing up values for same year

Bhargav_Movva — Fri, 24 Feb 2017 17:35:13 GMT

How a -ve sign in your code is showing the impact .pls explain in detail

Re: Summing up values for same year

Yavuz — Fri, 24 Feb 2017 20:38:06 GMT

@Bhargav_Movva Sorry it means - means -1*

cumtotsum + (-1*first.year * cumtotsum) + totalsum;

This works. When first.year changes it equal to "1" and it makes cumtotsum =0.
Otherwise first.year=0 and cumulative sum works.

Year totalsum first.year cimsumtot
==== ======= ======= =========
2014 10 1 10
2014 20 0 30
2015 10 1 10
2016 50 1 50
2016 60 0 110
2016 80 0 190