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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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

trphelps15
Fluorite | Level 6

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)

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 1938 views
  • 0 likes
  • 2 in conversation