topic Re: dates conversion in SAS Procedures

dates conversion

Way2go — Sat, 27 Oct 2012 15:36:57 GMT

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

Re: dates conversion

Reeza — Sat, 27 Oct 2012 16:05:36 GMT

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;

Re: dates conversion

art297 — Sat, 27 Oct 2012 16:06:15 GMT

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.

Re: dates conversion

Way2go — Sat, 27 Oct 2012 16:30:22 GMT

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 ). 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

Re: dates conversion

Way2go — Sat, 27 Oct 2012 16:37:28 GMT

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

Re: dates conversion

art297 — Sat, 27 Oct 2012 19:05:19 GMT

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;

Re: dates conversion

ghastly_kitten — Mon, 29 Oct 2012 11:12:58 GMT

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.

Re: dates conversion

Way2go — Mon, 29 Oct 2012 11:19:21 GMT

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