DATA Step, Macro, Functions and more

Summing up values for same year

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 112
Accepted Solution

Summing up values for same year

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!


Accepted Solutions
Solution
‎02-24-2017 09:32 AM
PROC Star
Posts: 7,492

Re: Summing up values for same year

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

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: Summing up values for same year

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

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 112

Re: Summing up values for same year

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

PROC Star
Posts: 7,492

Re: Summing up values for same year

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

 

Trusted Advisor
Posts: 1,586

Re: Summing up values for same year

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.

Frequent Contributor
Posts: 112

Re: Summing up values for same year

[ Edited ]

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.

Contributor
Posts: 54

Re: Summing up values for same year

I didnt tried but maybe give alternative idea,

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

Re: Summing up values for same year

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

Solution
‎02-24-2017 09:32 AM
PROC Star
Posts: 7,492

Re: Summing up values for same year

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

Frequent Contributor
Posts: 112

Re: Summing up values for same year

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.

Frequent Contributor
Posts: 112

Re: Summing up values for same year

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

Contributor
Posts: 54

Re: Summing up values for same year

@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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 213 views
  • 1 like
  • 5 in conversation