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
- /
- Dataset Collapse and Sum Calculation by Two Classe...

Topic Options

- 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
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-19-2017 10:55 AM

I'm attempting to collapse a dataset and calculate new summary variables by two classes...The data I'm working with is made up of a year's worth of observations split into hours, i.e.

ID Date Time Value

1 1/1/2014 0:00 5

1 1/1/2014 1:00 5

...

1 1/2/2014 0:00 10

1 1/2/2014 1:00 10

...

2 1/1/2014 0:00 5

2 1/1/2014 1:00 5

...

What I want to do is collapse the dataset by ID and time while adding variable counts and sums, i.e.

ID Time N Sum

1 0:00 2 15

1 1:00 2 15

...

I've tried a few different ways, proc sql, proc means, and if-else statements within a data step, but proc means has been the closest.

proc means data=dataset sum;

var value;

classes ID time;

output out=newdataset;

run;

This gives me the right values, but since there are multiple times within each ID class, there are blanks (see attached image). So the output dataset appears to re-group, re-calculate, and include additional statistics...is there any way to just get the table that is produced from the proc means statement as a new dataset and fill in the ID (tmc_code) values? If not, is there another way to collapse the dataset while still getting the values I need?

The other method I tried was proc sql, however I'm definitely unfamiliar with proc sql so I'm not sure what the issue is. The output is simply a new variable (sum_value) with the total of all values, not categorized by either ID or time.

proc sql;

create table newdataset as

select *, sum(value) as sum_value

from dataset

group by ID and time;

quit;

proc print (etc)...

This is a task that I will likely be doing a lot (collapsing and calculating new variables) so I really appreciate the help. Thank you!

Accepted Solutions

Solution

10-19-2017
11:16 AM

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

Posted in reply to trphelps15

10-19-2017 10:59 AM

Look at the examples in the PROC MEANS documentation, there's several that are exactly what you're looking for, specifically Example 2 and 10.

So yes, everything you want is possible with PROC MEANS.

Your SQL query is incorrect, so I suspect if you check your log, you'll see it had errors and never completed. Or you pasted incorrect code, but since it's all we can see...

All Replies

Solution

10-19-2017
11:16 AM

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

Posted in reply to trphelps15

10-19-2017 10:59 AM

Look at the examples in the PROC MEANS documentation, there's several that are exactly what you're looking for, specifically Example 2 and 10.

So yes, everything you want is possible with PROC MEANS.

Your SQL query is incorrect, so I suspect if you check your log, you'll see it had errors and never completed. Or you pasted incorrect code, but since it's all we can see...

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

Posted in reply to Reeza

10-19-2017 11:19 AM

Example 10 was definitely what I was looking for. Now on to creating one more new variable and then transforming the table...thank you very much for your help! (the sql ran btw, but it was nowhere near correct...that'll likely be some training down the road)