BookmarkSubscribeRSS Feed
TD-A
Fluorite | Level 6

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

 

 

6 REPLIES 6
Astounding
PROC Star

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?

TD-A
Fluorite | Level 6

 

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

 

Astounding
PROC Star

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.

TD-A
Fluorite | Level 6

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

Astounding
PROC Star

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

TD-A
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1150 views
  • 1 like
  • 2 in conversation