How to consolidate multiple weeks over 4 years into week numbers?

Reply
New Contributor
Posts: 3

How to consolidate multiple weeks over 4 years into week numbers?

Hi,

We have data spanning over 4 years that is organized by Week_of, which starts each week on a Monday. The data also has two other variables, impressions and calls We want to consolidate the data across the 4 years into week numbers.

For example:

Week 1 of 2014 started on Dec 30, and Week 1 of 2015 started on December 29. We want to sum the impression and call data for week 1 of 2014 and 2015 and combine them into one row - week 1, rather than two separate weeks.

Any help would be much appreciated

Thanks,

Super User
Posts: 19,878

Re: How to consolidate multiple weeks over 4 years into week numbers?

Posted in reply to Moses5488

What does your data look like, and how are you calculating weeks?

New Contributor
Posts: 3

Re: How to consolidate multiple weeks over 4 years into week numbers?

Sorry for not originally providing it!  Below is a sample set of data. The second chart is how we would like to construct it.

DateCallsImpressions
12/30/2013286660
12/29/2014458734
1/6/2014254135
1/5/2015418041
12/30/2013225276
12/29/2014335147
1/6/2014228790
1/5/2015404429

Week NumberCallsImpressions
Week 112825817
Week 212825395
Super User
Posts: 5,518

Re: How to consolidate multiple weeks over 4 years into week numbers?

Posted in reply to Moses5488

I would create a format based on your date ranges, then use the format to summarize.  For example:

data ranges;

   fmtname = 'weeks';

   do week = 1 to 60;

      start = '30dec2013'd + 28 * (week-1);

      end = start + 27;
      label = put(week, z2.);

      output;

   end;

run;

proc format cntlin=ranges;

run;

That will create your format.  Then apply it to summarize:

proc summary data=have nway;

   class date;

   format date weeks.;

   var calls impressions;

   output out=want (drop=_type_ _freq_) sum=;

run;

The code is untested, so you may need to tweak it a bit.  But the approach should be fine.  Note that WEEKS in the output data set still refers to a particular day, so you need to apply the format when printing to have it print as the week number.

Good luck.

Respected Advisor
Posts: 3,799

Re: How to consolidate multiple weeks over 4 years into week numbers?

Posted in reply to Moses5488

Use the WEEKV3. format associated with DATE.

New Contributor
Posts: 3

Re: How to consolidate multiple weeks over 4 years into week numbers?

Posted in reply to data_null__

Thanks for the help. We've made some changes to our data and would like to incorporate market data along with the call through rate (calls/impressions). Below is an example of our raw data. The second chart is where we are stuck at - the variable in this chart is the call through rate which we were able to caluculate. The third chart is what we'd like our end result to be.

MarketWeek_OfCallsImpressions
Anchorage12/30/2013286660
Anchorage12/29/2014458734
Anchorage1/6/2014254135
Anchorage1/5/2015418041
Anchor Point12/30/2013225276
Anchor Point12/29/2014335147
Anchor Point1/6/2014228790
Anchor Point1/5/2015404429

Week_OfAnchorageAnchor Point
12/30/20130.0040.004
12/29/20140.0050.006
1/6/20140.0060.003
1/5/20150.0050.009

Week_OfAnchorageAnchor Point
Week 10.0050.005
Week 20.0050.005

Thanks again for the help!

Super User
Posts: 5,518

Re: How to consolidate multiple weeks over 4 years into week numbers?

Posted in reply to Moses5488

Well, you would start out with the same format but then change the summarization slightly:

class date market;

Then you would have to reformat the output of the summarization:

data want2;

   set want;

   rate = calls / impressions;

   week = put(date, week.);

run;

proc transpose data=want2 out=want3;

   var rate;

   by week;

   id market;

run;

Finally, print WANT3.

SAS will change the variable names as it needs to.  For example, "Anchor Point" might become "Anchor_Point".

Ask a Question
Discussion stats
  • 6 replies
  • 308 views
  • 8 likes
  • 4 in conversation