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

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

mkt_apprentice — Tue, 10 Apr 2018 15:19:43 GMT

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

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

Reeza — Tue, 10 Apr 2018 15:23:27 GMT

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

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

SuryaKiran — Tue, 10 Apr 2018 15:25:49 GMT

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;

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

mkt_apprentice — Tue, 10 Apr 2018 15:28:37 GMT

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?

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

Astounding — Tue, 10 Apr 2018 15:53:07 GMT

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?

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

PaigeMiller — Tue, 10 Apr 2018 15:55:23 GMT

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.

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

fy1 — Tue, 10 Apr 2018 16:06:03 GMT

***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;

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

Cynthia_sas — Tue, 10 Apr 2018 16:08:24 GMT

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-groups/td-p/450169

Cynthia

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

Reeza — Tue, 10 Apr 2018 16:08:34 GMT

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

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