topic Re: Convert full date to year and week of the year? in SAS Procedures

Convert full date to year and week of the year?

efunk — Tue, 17 May 2011 14:28:39 GMT

Ok, let's say I have imported retail sales dataset with date formated like this:

2009.01.05
2009.01.06
...
2010.01.05
...
2011.01.05

I want to work with weekly results of sales, how to I convert this date format to something like this:
2009w1
2009w1
...
2010w1
...
2011w1
etc...

Or if that's not possible, maybe change date to corresponding weeks Sunday date.
for example if 2009.01.19 is 2nd weeks Saturday, change all dates:
2009.01.13
2009.01.14
2009.01.15
2009.01.16
2009.01.17
2009.01.18

to 2009.01.19

Help much appreciated.

Message was edited by: efunk Message was edited by: efunk

Re: Convert full date to year and week of the year?

SPR — Tue, 17 May 2011 14:43:47 GMT

Hello Efunk,

Is this what you need?
[pre]
data i;
input date ANYDTDTE10.;
format date YYMMDDP10.;
date1=SUBSTR(put(date,WEEKU9.),1,6);
datalines;
2009.01.05
2009.01.06
2010.01.05
2011.01.05
run;
[/pre]
Sincerely,
SPR

Re: Convert full date to year and week of the year?

darrylovia — Tue, 17 May 2011 14:50:54 GMT

SAS has a number of date functions and formats that you could use to solve your problem. Try the WEEK function or WEEKU./WEEKW. formats

Try looking at the documentation
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003154994.htm
http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002604217.htm

below is a quite example for week begining with Sunday

data one;
format raw_date yymmddp10.;
input raw_date yymmdd10.;
datalines;
2009.01.05
2009.01.06
2010.01.05
2011.01.05
;
run;

data two;
set one;
format myweek weeku6.;
myweek=raw_date;
run;

Re: Convert full date to year and week of the year?

efunk — Tue, 17 May 2011 14:59:18 GMT

Both of your methods works good.
I went with darrylovia's method.

Thank you, guys.

Re: Convert full date to year and week of the year?

efunk — Tue, 17 May 2011 15:10:32 GMT

Ok, I might have been to excited about this and jumped to the conclusions too quickly.
SPR, your method isn't working as it should, I'm getting W0(there is no such thing as week 0), and only up to W5 in the whole year. I'm guessing it converted to something else.

And darrylovia, maybe you know, why proc SQL doesn't group by with your format?

Re: Convert full date to year and week of the year?

SPR — Tue, 17 May 2011 15:41:23 GMT

Sorry it is a typo in my code. Should be
date1=SUBSTR(put(date,WEEKU9.),1,7);
SPR

Re: Convert full date to year and week of the year?

efunk — Tue, 17 May 2011 16:40:34 GMT

Works like a charm this time.
Thank you, SPR.

Re: Convert full date to year and week of the year?

darrylovia — Tue, 17 May 2011 17:58:15 GMT

Just convert the date to a character like below

data two;
set one;
format myweek weeku6.;
myweek=raw_date;
cat_week=put(raw_date,weeku6.);
run;