topic Re: Weekly counts based on start and end dates in SAS Programming

Weekly counts based on start and end dates

TD-A — Wed, 24 May 2017 16:26:13 GMT

Hi,

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?

thanks,

TD-A

Re: Weekly counts based on start and end dates

Astounding — Wed, 24 May 2017 16:49:13 GMT

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?

Re: Weekly counts based on start and end dates

TD-A — Wed, 24 May 2017 17:17:28 GMT

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,

e.g.

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..

Re: Weekly counts based on start and end dates

Astounding — Wed, 24 May 2017 17:30:35 GMT

OK, here's a way to set up a format that can be used to group:

data format_me;

fmtname = 'MyWeeks';

do week=1 to 500;

start = '28feb2016'd + (week-1) * 7;

end = start + 6;

label = put(start, yymmdd10.) || ' to ' || put(end, yymmdd10.);

output;

end;

run;

proc format cntlin=format_me;

run;

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.

Re: Weekly counts based on start and end dates

TD-A — Wed, 24 May 2017 19:16:47 GMT

Thanks, Astounding.

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..

Re: Weekly counts based on start and end dates

Astounding — Wed, 24 May 2017 19:23:12 GMT

You might have to show the SAS log. It's a little difficult to visualize what's happening here without it.

Re: Weekly counts based on start and end dates

TD-A — Tue, 30 May 2017 12:30:57 GMT

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.

Thanks again