Help using Base SAS procedures

Dataset Collapse and Sum Calculation by Two Classes

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Dataset Collapse and Sum Calculation by Two Classes

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
Super User
Posts: 23,663

Re: Dataset Collapse and Sum Calculation by Two Classes

Posted in reply to trphelps15

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...

 

http://documentation.sas.com/?docsetId=proc&docsetTarget=n1qnc9bddfvhzqn105kqitnf29cp.htm&docsetVers...

View solution in original post


All Replies
Solution
‎10-19-2017 11:16 AM
Super User
Posts: 23,663

Re: Dataset Collapse and Sum Calculation by Two Classes

Posted in reply to trphelps15

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...

 

http://documentation.sas.com/?docsetId=proc&docsetTarget=n1qnc9bddfvhzqn105kqitnf29cp.htm&docsetVers...

Occasional Contributor
Posts: 6

Re: Dataset Collapse and Sum Calculation by Two Classes

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)

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 236 views
  • 0 likes
  • 2 in conversation