Hi SAS community,
I have a large data set with records of walking activity.
Indvl ID Date Walk distance (miles)
01 000001 20000101 2
02 000001 20000102 1
03 000001 20000103 4
04 000001 20000104 0
...
31 000001 20000131 4
32 000002 20000101 0
33 000002 20000102 0
34 000002 20000103 2
35 000002 20000104 3
....
I want to aggregate these records into monthly level using base SAS. How could I do that without using SAS Enterprise?
Thanks
You can do this in different ways, since you have large dataset then Proc SQL might be efficient way. Select the appropriate group by variables as needed.
PROC SQL;
create table want as
select indvl,id,date,SUM(Walk_Distance) as Monthly_Walk
FROM HAVE
GROUP BY ID,DATE;
QUIT;
Do you have a SAS ETS license? If so, PROC TIMESERIES is what you're looking for.
What do you mean by SAS 'Enterprise', are you referring to SAS Enterprise Guide? If so, are you using SAS Studio or SAS Base and are programming? SAS Studio has it's own set of tasks, though they differ from EG the concept is the same.
@mkt_apprentice wrote:
Hi SAS community,
I have a large data set with records of walking activity.
Indvl ID Date Walk distance (miles)
01 000001 20000101 2
02 000001 20000102 1
03 000001 20000103 4
04 000001 20000104 0
...
31 000001 20000131 4
32 000002 20000101 0
33 000002 20000102 0
34 000002 20000103 2
35 000002 20000104 3
....
I want to aggregate these records into monthly level using base SAS. How could I do that without using SAS Enterprise?
Thanks
I have SAS Studio and SAS for Window. I was searching over the internet how to aggregate panel data and some sources mention ways to solve the task using SAS Enterprise Guide. The dataset is a panel data set and has more than just one variable (I only mention one variable - walking distance- here to illustrate my point). How can I know if I have SAS ETS or not? Also can I use SAS proc timeseries for the panel data?
You can do this in different ways, since you have large dataset then Proc SQL might be efficient way. Select the appropriate group by variables as needed.
PROC SQL;
create table want as
select indvl,id,date,SUM(Walk_Distance) as Monthly_Walk
FROM HAVE
GROUP BY ID,DATE;
QUIT;
Just a couple of questions, then the programming will be easy ...
Is DATE a numeric variable or a character variable?
If numeric, does the value reflect a SAS date or is it "what you see is what you get"?
Do you want a separate aggregation for each ID (per month), or do you want just one summary record combining all IDs for each month?
What does "aggregate" mean, anyway?
Does it mean "sum"? Does it mean "average"? Does it mean that you want to put all of the information into a long wide observation?
Tell us, we need to know.
You can use PROC MEANS to aggregate, see the example here, which combines monthly data to annual data:
https://gist.github.com/statgeek/0cae5568752959b035516d6ac07a20fb
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.