turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- how to sum within column with 2 sorting variables?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-07-2011 11:07 AM

Hello,

Suppose i have such dataset :

date id res sumres

1 a 10

1 a 15

1 a 10 35

2 a 12

2 a 8

2 a 10 30

1 b 10

1 b 17

1 b 12 39

;

I need to calculate these sumres (35, 30, 39....) in a new dataset which are for id=a and date=1, for id=a and date=2, id=b and date=1, id=b=2 and so on... i have dates from 1...30.

its probably a loop, if first.date=....do;..but cant figure it out.

anyone knows how to do it? i'd be immesely grateful.

cheers,

M

Accepted Solutions

Solution

08-07-2011
11:23 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-07-2011 11:23 AM

If your data are EXACTLY as shown, and you really only want the sumres calculation to be non-missing for the last record in each set, then it is easy. I don't have access to SAS to test the following code at the moment, but it should give you enough of an idea to solve your problem.

data want (drop=calculation);

set have;

by id date;

if first.date then calculation=res;

else calculation+res;

if last.date then sumres=calculation;

run;

All Replies

Solution

08-07-2011
11:23 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-07-2011 11:23 AM

If your data are EXACTLY as shown, and you really only want the sumres calculation to be non-missing for the last record in each set, then it is easy. I don't have access to SAS to test the following code at the moment, but it should give you enough of an idea to solve your problem.

data want (drop=calculation);

set have;

by id date;

if first.date then calculation=res;

else calculation+res;

if last.date then sumres=calculation;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

08-07-2011 11:56 AM

art297, thank you for a quick reply - you're a star! i tried it before but missed out the ' if last.date' part