BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
devsas
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

11 REPLIES 11
art297
Opal | Level 21
proc summary data=have nway;
  by code description year notsorted;
  var totalSum totalUnits;
  output out=want (drop=_:) sum=;
run;

Art, CEO, AnalystFinder.com

 

devsas
Pyrite | Level 9

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

art297
Opal | Level 21

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

 

Shmuel
Garnet | Level 18

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.

devsas
Pyrite | Level 9

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.

Yavuz
Quartz | Level 8
I didnt tried but maybe give alternative idea,

data want;
set have;
by code description year;
cumtotsum + (-first.year * cumtotsum) + totalsum;
run;
Bhargav_Movva
Fluorite | Level 6

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

art297
Opal | Level 21

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

devsas
Pyrite | Level 9

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.

devsas
Pyrite | Level 9

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

Yavuz
Quartz | Level 8
@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

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
  • 11 replies
  • 1157 views
  • 1 like
  • 5 in conversation