topic Re: Week# in SAS Programming

Week#

newbi — Tue, 23 Aug 2011 14:56:47 GMT

I have table with following columns:

WeekEndDate - Product ID - Sales

I would like to create new column which can give me Week# from the first week of sales for example:

If the first week of sales is on 23OCT2010 then the new column data value will be Week 1

if the 2nd of week of sales is on 06Nov2010 then the value will be Week 2

Currently the week end date format is DDMMYYYY (06NOV2010)

Thanks

Week#

art297 — Tue, 23 Aug 2011 15:07:03 GMT

I think you have to provide more of an explanation and more data that reflects what you really want to accomplish. I would think that 06Nov2010, in your example, would be week 3.

If you are just trying to convert your data to reflect their variable name (e.g. first_week, second_week, etc.) you could do that with an array.

Week#

newbi — Tue, 23 Aug 2011 15:11:58 GMT

Yes, that's what i'm trying to accomplish - reflect the valriable name week1 week2 week3...based on product launch date.

Thanks

Week#

art297 — Tue, 23 Aug 2011 15:16:09 GMT

Provide a sample data file showing what you have and what you want. Since you didn't indicate in your previous example whether 06Nov2010 should be week2 or week3, the forum can only guess.

Week#

newbi — Tue, 23 Aug 2011 15:29:48 GMT

Is there a way to attached the sample data file in discussion ?

Week#

newbi — Tue, 23 Aug 2011 15:36:17 GMT

LaunchDate	week_end_date	Product_Id	Activation_amt
20101102	13Nov2010	100	$300.00
20101102	20Nov2010	100	$600.00
20101102	27Nov2010	100	$1,200.00
20101102	04Dec2010	100	$1,200.00
20101102	11Dec2010	100	$1,500.00
20101102	18Dec2010	100	$1,500.00
20101102	25Dec2010	100	$1,500.00
20101102	01Jan2011	100	$2,000.00
20101102	08Jan2011	100	$2,000.00
20101102	15Jan2011	100	$2,000.00
20101102	22Jan2011	100	$5,000.00
20101102	29Jan2011	100	$5,000.00
20101102	05Feb2011	100	$5,000.00
20101102	12Feb2011	100	$5,000.00
20101102	19Feb2011	100	$5,000.00
20101130	04Dec2010	200	$300.00
20101130	11Dec2010	200	$600.00
20101130	18Dec2010	200	$1,200.00
20101130	25Dec2010	200	$1,200.00
20101130	01Jan2011	200	$1,500.00
20101130	08Jan2011	200	$1,500.00
20101130	15Jan2011	200	$1,500.00
20101130	22Jan2011	200	$2,000.00
20101130	29Jan2011	200	$2,000.00
20101130	05Feb2011	200	$2,000.00
20101130	12Feb2011	200	$5,000.00
20101130	19Feb2011	200	$5,000.00

Week#

art297 — Tue, 23 Aug 2011 15:43:36 GMT

I'd need to see one more column, namely the week numbers that you would want for each of the rows.

Week#

newbi — Tue, 23 Aug 2011 16:30:34 GMT

LaunchDate	week_end_date	Product_Id	Activation_amt	Week#
20101102	13Nov2010	100	$300.00	Week1
20101102	20Nov2010	100	$600.00	Week2
20101102	27Nov2010	100	$1,200.00	Week3
20101102	04Dec2010	100	$1,200.00	Week4
20101102	11Dec2010	100	$1,500.00	Week5
20101102	18Dec2010	100	$1,500.00	Week6
20101102	25Dec2010	100	$1,500.00	Week7
20101102	01Jan2011	100	$2,000.00	Week8
20101102	08Jan2011	100	$2,000.00	Week9
20101102	15Jan2011	100	$2,000.00	Week10
20101102	22Jan2011	100	$5,000.00	Week11
20101102	29Jan2011	100	$5,000.00	Week12
20101102	05Feb2011	100	$5,000.00	Week13
20101102	12Feb2011	100	$5,000.00	Week14
20101102	19Feb2011	100	$5,000.00	Week15
20101130	04Dec2010	200	$300.00	Week1
20101130	11Dec2010	200	$600.00	Week2
20101130	18Dec2010	200	$1,200.00	Week3
20101130	25Dec2010	200	$1,200.00	Week4
20101130	01Jan2011	200	$1,500.00	Week5
20101130	08Jan2011	200	$1,500.00	Week6
20101130	15Jan2011	200	$1,500.00	Week7
20101130	22Jan2011	200	$2,000.00	Week8
20101130	29Jan2011	200	$2,000.00	Week9
20101130	05Feb2011	200	$2,000.00	Week10
20101130	12Feb2011	200	$5,000.00	Week11
20101130	19Feb2011	200	$5,000.00	Week12

Week#

art297 — Tue, 23 Aug 2011 16:43:02 GMT

As long as your data are sorted by product_id week_end_date how about something like:

data want;

set have;

by Product_Id;

if first.Product_Id then Counter=0;

Counter+1;

Week_Number=catt('Week',Counter);

run;

Re: Week#

DLing — Tue, 23 Aug 2011 19:32:04 GMT

Something like this should suite your needs.

- The duration from Launch to Weekend is determined by intck() function, modify to fit your needs as intck() is very flexible with date handling.

- Personally, I'd keep the period column instead of Weekno because sorting on on Prod-Period would give correct sequential results, but sorting on Prod-Weekno won't. This is because you'd get 'Week1', 'Week10', 'Week11', before 'Week2' in the standard alphabet sorting sequence.

data sales;

input Launch:yymmdd8. Weekend:date9. Prod Sales;

format Launch yymmdd10. weekend yymmdd10. sales dollar10. ;

Period = intck('week',launch,weekend);

Weekno = cats('Week',put(period,4.));

cards;

20101102 13Nov2010 100 300.00

20101102 20Nov2010 100 600.00

20101102 27Nov2010 100 1200.00

20101102 04Dec2010 100 1200.00

20101102 11Dec2010 100 1500.00

20101102 18Dec2010 100 1500.00

;

Re: Week#

art297 — Tue, 23 Aug 2011 19:50:59 GMT

I didn't suggest the intck route as the OP had indicated (or at least I thought I understood) that order not date implied week#. Regardles, if the OP doesn't have any missing weeks, both sets of code will provide the desired result.

However, I'm writing because of your comment that one can't sort the data the way the OP wanted it formatted.

With numeric collation these days, such problems can be easily overcome. E.g.:

proc sort data=want sortseq=linguistic (numeric_collation=on);

by Product_Id Week_Number;

run;

Re: Week#

DLing — Tue, 23 Aug 2011 20:00:31 GMT

Thanks, Art. I didn't know about the sorting options, and it is very good to know. Sadly, I'm still stuck in the old days runnning SAS 9.1.3 as IT is very slow in upgrading.

Week#

newbi — Tue, 23 Aug 2011 20:00:57 GMT

Thanks for all the reply. This solution works for me:

data want;

set have;

by Product_Id;

if first.Product_Id then Counter=0;

Counter+1;

Week_Number=catt('Week',Counter);

run;

I will try out the other solution as well to see what result i get.

Again, thanks for all your help.

Re: Week#

DLing — Tue, 23 Aug 2011 20:09:38 GMT

WIth SAS, there are always multiple ways to get things done, which is a good thing. Calculating period from dates is more work, but is more resistant to changes to input data such as if the sorting order isn't preserved, or if records went missing in the middle. Using counter is definitely fast and correct if no data issues crop up. Pick whatever suits your data environment. Having been bitten multiple times with sloppy input data streams, one picks up defensive programming habits quickly.