DATA Step, Macro, Functions and more

Weekly counts based on start and end dates

Reply
Occasional Contributor
Posts: 10

Weekly counts based on start and end dates

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

 

 

Super User
Posts: 5,081

Re: Weekly counts based on start and end dates

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?

Occasional Contributor
Posts: 10

Re: Weekly counts based on start and end dates

 

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

 

Super User
Posts: 5,081

Re: Weekly counts based on start and end dates

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.

Occasional Contributor
Posts: 10

Re: Weekly counts based on start and end dates

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

Super User
Posts: 5,081

Re: Weekly counts based on start and end dates

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

Occasional Contributor
Posts: 10

Re: Weekly counts based on start and end dates

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

Ask a Question
Discussion stats
  • 6 replies
  • 111 views
  • 1 like
  • 2 in conversation