Help using Base SAS procedures

Convert full date to year and week of the year?

Reply
Occasional Contributor
Posts: 10

Convert full date to year and week of the year?

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
Super Contributor
Super Contributor
Posts: 365

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

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
Frequent Contributor
Posts: 139

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

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;
Occasional Contributor
Posts: 10

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

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

Thank you, guys.
Occasional Contributor
Posts: 10

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

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?
Super Contributor
Super Contributor
Posts: 365

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

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

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

Works like a charm this time.
Thank you, SPR.
Frequent Contributor
Posts: 139

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

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;
Ask a Question
Discussion stats
  • 7 replies
  • 2834 views
  • 0 likes
  • 3 in conversation