DATA Step, Macro, Functions and more

Week#

Reply
SAS Employee
Posts: 73

Week#

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

PROC Star
Posts: 7,363

Week#

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.

SAS Employee
Posts: 73

Week#

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

Thanks

PROC Star
Posts: 7,363

Week#

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.

SAS Employee
Posts: 73

Week#

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

SAS Employee
Posts: 73

Week#

LaunchDateweek_end_dateProduct_IdActivation_amt
2010110213Nov2010100$300.00
2010110220Nov2010100$600.00
2010110227Nov2010100$1,200.00
2010110204Dec2010100$1,200.00
2010110211Dec2010100$1,500.00
2010110218Dec2010100$1,500.00
2010110225Dec2010100$1,500.00
2010110201Jan2011100$2,000.00
2010110208Jan2011100$2,000.00
2010110215Jan2011100$2,000.00
2010110222Jan2011100$5,000.00
2010110229Jan2011100$5,000.00
2010110205Feb2011100$5,000.00
2010110212Feb2011100$5,000.00
2010110219Feb2011100$5,000.00
2010113004Dec2010200$300.00
2010113011Dec2010200$600.00
2010113018Dec2010200$1,200.00
2010113025Dec2010200$1,200.00
2010113001Jan2011200$1,500.00
2010113008Jan2011200$1,500.00
2010113015Jan2011200$1,500.00
2010113022Jan2011200$2,000.00
2010113029Jan2011200$2,000.00
2010113005Feb2011200$2,000.00
2010113012Feb2011200$5,000.00
2010113019Feb2011200$5,000.00
PROC Star
Posts: 7,363

Week#

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

SAS Employee
Posts: 73

Week#

LaunchDateweek_end_dateProduct_IdActivation_amtWeek#
2010110213Nov2010100$300.00 Week1
2010110220Nov2010100$600.00 Week2
2010110227Nov2010100$1,200.00 Week3
2010110204Dec2010100$1,200.00 Week4
2010110211Dec2010100$1,500.00 Week5
2010110218Dec2010100$1,500.00 Week6
2010110225Dec2010100$1,500.00 Week7
2010110201Jan2011100$2,000.00 Week8
2010110208Jan2011100$2,000.00 Week9
2010110215Jan2011100$2,000.00 Week10
2010110222Jan2011100$5,000.00 Week11
2010110229Jan2011100$5,000.00 Week12
2010110205Feb2011100$5,000.00 Week13
2010110212Feb2011100$5,000.00 Week14
2010110219Feb2011100$5,000.00 Week15
2010113004Dec2010200$300.00 Week1
2010113011Dec2010200$600.00 Week2
2010113018Dec2010200$1,200.00 Week3
2010113025Dec2010200$1,200.00 Week4
2010113001Jan2011200$1,500.00 Week5
2010113008Jan2011200$1,500.00 Week6
2010113015Jan2011200$1,500.00 Week7
2010113022Jan2011200$2,000.00 Week8
2010113029Jan2011200$2,000.00 Week9
2010113005Feb2011200$2,000.00 Week10
2010113012Feb2011200$5,000.00 Week11
2010113019Feb2011200$5,000.00 Week12
PROC Star
Posts: 7,363

Week#

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;

Frequent Contributor
Posts: 104

Re: Week#

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 

;

PROC Star
Posts: 7,363

Re: Week#

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;

Frequent Contributor
Posts: 104

Re: Week#

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.

SAS Employee
Posts: 73

Week#

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.

Frequent Contributor
Posts: 104

Re: Week#

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. 

Ask a Question
Discussion stats
  • 13 replies
  • 243 views
  • 1 like
  • 3 in conversation