BookmarkSubscribeRSS Feed
efunk
Calcite | Level 5
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
7 REPLIES 7
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
darrylovia
Quartz | Level 8
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;
efunk
Calcite | Level 5
Both of your methods works good.
I went with darrylovia's method.

Thank you, guys.
efunk
Calcite | Level 5
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?
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Sorry it is a typo in my code. Should be
date1=SUBSTR(put(date,WEEKU9.),1,7);
SPR
efunk
Calcite | Level 5
Works like a charm this time.
Thank you, SPR.
darrylovia
Quartz | Level 8
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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 8449 views
  • 0 likes
  • 3 in conversation