BookmarkSubscribeRSS Feed
Way2go
Calcite | Level 5

Hello,

I have a date in my dataset in format DD/MM/YYYY and i have to convert it in two different variables:

  • the first is the variable WEEK and has to be YYYYWW where WW is the number of the week of the year YYYY
  • the second is WEEK_T and has to be in format DD/MM/YYYY where the day must be the sunday of the corresponding week.

How can I do it?

I'll leave you an example:

DATE: 08/07/2011 (it's a friday)

WEEK: 201127

WEEK_T: 10/07/2011 (it's the sunday of the week #27 of 2011)

Thank you very much for your help Smiley Happy

7 REPLIES 7
Reeza
Super User

I think week_t should be 3/7/2011 rather than 10.


data want;

date='08Jul2011'd;

week_date=week(date);

week_formatted=year(date)*100+week_date;

sunday=intnx('week', date, 0, 'beginning');

test='03Jul2011'd;

test_week=week(test);

format sunday test date date9.;

run;

Way2go
Calcite | Level 5

Ok, right. I have made a bad formulation of the problem. I don't have a single date, but a variable date (DD/MM/YYYY), week and week_t.

Imagine the dataset made this way:

DATE             WEEK    WEEK_T

08/07/2011          .              .

16/07/2011          .              .

etc etc for about 8 millions observation (so i can't put this line date='08Jul2011'd; for every date in my dataset Smiley Wink). How can i find the values for week and week_t?

the value of week_t in my example should be 10/07/2011 because the week starts on monday.

Thanks for your help Smiley Happy

art297
Opal | Level 21

You have to define week first.  Does it begin with Jan1st?  Is the first week 0 or 1 or dependent upon whether the first week began during the previous year?  Does each week begin on a particular day of the week?

Given your example, I would guess that you consider Monday to be the first day of the week and that you consider the first week to be numbered 0 or something else.

Way2go
Calcite | Level 5

Each week starts on monday and finishes on sunday.

The week #1 of 2011 is the one that ends on the first sunday of january 2011, so i don't have a week 0.

I hope I've made this more clear to you

art297
Opal | Level 21

If you can live with a slightly different format for week_t one solution might be:

data have;

  format thedate date.;

  format week_t ddmmyy10.;

  do thedate='01jan2011'd to '31dec2011'd;

    week=put(thedate+7,weekw11.);

    week_t=intnx('week.2',thedate,0,'end');

    output;

  end;

run;

ghastly_kitten
Fluorite | Level 6

Ok...

Here's the compilation of Arthur's and Reeza variants with additional comments.

data ResultSet;

     set YourSourceDataSetWith8MillionRows;

     /* get the sunday of the week, which starts on monday. The number of the weeks in SAS are determined according to ISO standart */

     week_t = intnx('week.2', date, 0, 'e');

     /* as there is no format which won't print 'W' letter in the date format of the YYYYWW kind you can use this, but beware! use comparison with 'week' accurately */

     week = year(week_t) * 100 + week (week_t) - 1;

     format week_t DDMMYY10.;

     format week 6.;

run;

... well, at least it fits to your source requirements.

Sorry for this 'compilation' of already solved problem, but I think this thread must be marked as 'Answered' and was sure it's due to problems with interpretation.

Way2go
Calcite | Level 5

Sorry for this 'compilation' of already solved problem, but I think this thread must be marked as 'Answered' and was sure it's due to problems with interpretation.


I saw all the answers but I haven't had the occasion to try them yet. As soon as I try them in SAS I won't hesitate to mark it answered Smiley Wink

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 1863 views
  • 0 likes
  • 4 in conversation