DATA Step, Macro, Functions and more

Question about how to aggregate record data to daily level in Base SAS

Reply
Occasional Contributor
Posts: 9

Question about how to aggregate record data to daily level in Base SAS

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

Super User
Posts: 23,296

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to mkt_apprentice

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


 

Occasional Contributor
Posts: 9

Re: Question about how to aggregate record data to daily level in Base SAS

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? 

Valued Guide
Posts: 559

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to mkt_apprentice

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
Super User
Posts: 6,632

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to mkt_apprentice

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?

Respected Advisor
Posts: 2,825

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to mkt_apprentice

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
SAS Super FREQ
Posts: 9,324

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to PaigeMiller
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
Occasional Contributor fy1
Occasional Contributor
Posts: 8

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to mkt_apprentice
***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;
Super User
Posts: 23,296

Re: Question about how to aggregate record data to daily level in Base SAS

Posted in reply to mkt_apprentice

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

 

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

Ask a Question
Discussion stats
  • 8 replies
  • 131 views
  • 1 like
  • 7 in conversation