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

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

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran

View solution in original post

8 REPLIES 8
Reeza
Super User

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


 

mkt_apprentice
Obsidian | Level 7

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? 

SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran
Astounding
PROC Star

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?

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Cynthia_sas
SAS Super FREQ
Hi,
In addition to the other advice you've gotten, there are several other SAS procedures that will summarize data -- PROC MEANS, PROC TABULATE and PROC REPORT, in addition to PROC SQL and finally, you can always summarize or aggregate the data yourself with a DATA step program.

We cover PROC MEANS in our Programming 1 course. We cover PROC TABULATE and PROC REPORT in our Report Writing 1 course. However, there have been many previous examples posted in the Forum for how to summarize data using those procedures.
Here are a few previous postings:
https://communities.sas.com/t5/SAS-Procedures/sums-and-counts-by-group/td-p/49055
https://communities.sas.com/t5/SAS-Procedures/Summarize-using-Proc-Report-when-there-are-3-or-more-g...

Cynthia
fy1
Fluorite | Level 6 fy1
Fluorite | Level 6
***you will need to convert Date into SAS date format ; ***the New Date variable is called as SAS_Date; ****aggregate by Month; proc means data=yourdata sum nway ; var Walk_distance; class Indvl_ID SAS_Date; format SAS_Date MMYY.; output out=aggr_by_mon sum=walk_dist; run;
Reeza
Super User

You can use PROC MEANS to aggregate, see the example here, which combines monthly data to annual data:

 

https://gist.github.com/statgeek/0cae5568752959b035516d6ac07a20fb

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!

How to Concatenate Values

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.

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
  • 8 replies
  • 1799 views
  • 3 likes
  • 7 in conversation