topic Aggregate by Week in SAS Programming

Aggregate by Week

sefcikm — Mon, 15 Mar 2021 13:53:49 GMT

I have a date field that was formatted as DATE9. and then I changed it to YYWEEKU7. I am now trying to aggregate by that new weekly formatted date but it is not aggregating properly. I think the day is still stored in the background so when I sum my values it is summing down to the daily level, instead of weekly level. Is there a way to work around this other than changing the field to a character field and summing by that?

Re: Aggregate by Week

ballardw — Mon, 15 Mar 2021 14:34:46 GMT

@sefcikm wrote:

I have a date field that was formatted as DATE9. and then I changed it to YYWEEKU7. I am now trying to aggregate by that new weekly formatted date but it is not aggregating properly. I think the day is still stored in the background so when I sum my values it is summing down to the daily level, instead of weekly level. Is there a way to work around this other than changing the field to a character field and summing by that?

Please show EXACTLY how you are "summing down". And example of some starting data and what you expect for a result is helpful.

I strongly suspect you are doing something manually in a data step that is trivial with one of the procedures like Means, Tabulate or Report.

Do you need a data set (for further manipulation) or a report that people read?

Re: Aggregate by Week

sefcikm — Mon, 15 Mar 2021 15:02:28 GMT

Below is a simplified version of what I am working with. The date field is a date9. format. I then took that date field and used it to make the week field by changing the format to yyweek07. I am using SAS Enterprise Guide for this and just changed the format in the query builder using 'Date' as my source column. I am now trying to sum the revenue across the week field to get something like the second table I shared below. When I got to sum though, it isn't summing to the week level. The output of this is just a dataset that I will then be working with further.

Date	Week	Revenue
01JAN2020	2020W01	500
02JAN2020	2020W01	200
01FEB2020	2020W04	300
02FEB2020	2020W04	200

Week	Revenue
2020W01	700
2020W04	500

Re: Aggregate by Week

ballardw — Mon, 15 Mar 2021 16:13:50 GMT

Can you show the code generated by the query builder?

And instead of "query builder" you might be looking for a "summarize" task