05-24-2017 12:26 PM
I want to do counts on a weekly basis beginning from 2016/03/01 to present date of all accounts from my table.
My table contains dates with the original date format of DDMONYYYY but I want the output layout to be in weekly format:
e.g. Sun, Feb 28, 2016 to Sat, Mar 5, 2016 and so on.. Can someone help me out, please?
05-24-2017 12:49 PM
This isn't difficult, but we really need to hear more from you to define the problem.
Is "your table" already a SAS data set?
Is your date variable a character string or does it hold numeric SAS date values?
Do you know how to get the report you want for each day individually, without grouping by week?
What else should the report contain?
05-24-2017 01:17 PM
Yes, my table is a SAS dataset.
My date variable is already in date format: date9.
Yes, I have a query to get the counts that I want, it’s the grouping by week I’m struggling with.
Basically, it will just contain weeks,
Week Vol attrition transfers to pbi transfers out of pbi
week1: 2016-02-28 to 2016-03-05 # of accts attrition # of transfer to pbi # of transfer out of pbi
week2: 2016-03-06 to 2016-03-12 # of accts attrition # of transfer to pbi # of transfer out of pbi
and so on..
05-24-2017 01:30 PM
OK, here's a way to set up a format that can be used to group:
fmtname = 'MyWeeks';
do week=1 to 500;
start = '28feb2016'd + (week-1) * 7;
end = start + 6;
label = put(start, yymmdd10.) || ' to ' || put(end, yymmdd10.);
proc format cntlin=format_me;
This gives you a format with 500 weeks defined, that you can apply to your summarization process. Depending on what that part of the program looks like you might be using:
format your_date_variable_name myweeks.;
But to get more specific than that, I'd need to see how you summarize the data to get your report.
05-24-2017 03:16 PM
So, I was able to set the weeks from week1 to current week from the format you sent below, but I can't incorporate it with my table.
My main table contains account (#s), dates from when they were extracted (daily), status (active, etc) and product name.
I want to count the # of accounts per week based on the formatted weeks below.
Weeks # of Accts (VOL)
Week1: 02/28/2016 - 03/05/2016 10
and so on..
05-30-2017 08:30 AM
Thanks for the help.
I didn't use the week format since there are lots of weeks that don't have counts so i used the specific dates instead.