BookmarkSubscribeRSS Feed
newbi
SAS Employee

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

13 REPLIES 13
art297
Opal | Level 21

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.

newbi
SAS Employee

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

Thanks

art297
Opal | Level 21

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.

newbi
SAS Employee

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

newbi
SAS Employee
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
art297
Opal | Level 21

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

newbi
SAS Employee
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
art297
Opal | Level 21

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;

DLing
Obsidian | Level 7

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 

;

art297
Opal | Level 21

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;

DLing
Obsidian | Level 7

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.

newbi
SAS Employee

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.

DLing
Obsidian | Level 7

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. 

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
  • 13 replies
  • 1179 views
  • 1 like
  • 3 in conversation