Hello,
I have a date in my dataset in format DD/MM/YYYY and i have to convert it in two different variables:
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
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;
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
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.
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
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;
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.
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
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.
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.