Help using Base SAS procedures

dates conversion

Reply
Occasional Contributor
Posts: 8

dates conversion

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

Super User
Posts: 17,831

Re: dates conversion

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;

Occasional Contributor
Posts: 8

Re: dates conversion

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

PROC Star
Posts: 7,363

Re: dates conversion

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.

Occasional Contributor
Posts: 8

Re: dates conversion

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

PROC Star
Posts: 7,363

Re: dates conversion

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;

Contributor
Posts: 41

Re: dates conversion

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.

Occasional Contributor
Posts: 8

Re: dates conversion

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

Ask a Question
Discussion stats
  • 7 replies
  • 722 views
  • 0 likes
  • 4 in conversation