02-04-2015 10:49 AM
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.
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
02-04-2015 11:00 AM
Sorry for not originally providing it! Below is a sample set of data. The second chart is how we would like to construct it.
02-04-2015 11:29 AM
I would create a format based on your date ranges, then use the format to summarize. For example:
fmtname = 'weeks';
do week = 1 to 60;
start = '30dec2013'd + 28 * (week-1);
end = start + 27;
label = put(week, z2.);
proc format cntlin=ranges;
That will create your format. Then apply it to summarize:
proc summary data=have nway;
format date weeks.;
var calls impressions;
output out=want (drop=_type_ _freq_) sum=;
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.
02-04-2015 01:31 PM
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.
Thanks again for the help!
02-04-2015 03:15 PM
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:
rate = calls / impressions;
week = put(date, week.);
proc transpose data=want2 out=want3;
Finally, print WANT3.
SAS will change the variable names as it needs to. For example, "Anchor Point" might become "Anchor_Point".