topic Re: Week/year formatting in SAS Programming

Week/year formatting

deleted_user — Tue, 05 Aug 2008 14:03:47 GMT

How do you collapse a date variable into a week/year format; for example: 2008 week 1? I need to create weeks 1 to 52 for several years (or 53 for some years) so years of trend data can be graphed on top one another. I can't seem to find this particular formatting in SAS help... Thanks!

Re: Week/year formatting

Olivier — Tue, 05 Aug 2008 14:33:22 GMT

You need to create such a format using the PICTURE statement in the Format procedure.
[pre]
PROC FORMAT ;
PICTURE wwyy
LOW-HIGH='%0Y week %0U' (DATATYPE=DATE)
;
RUN ;
PROC SQL INOBS=15 ;
SELECT date FORMAT=wwyy., *
FROM sashelp.air ;
QUIT ;
[/pre]
%0Y stands for "year on 4 digits" and %0U for "number of the week on 2 digits".
Olivier

Re: Week/year formatting

deleted_user — Tue, 05 Aug 2008 18:47:23 GMT

Is there a way to create a date variable that only includes week/year (rather than making it a format)? In stata you can just do: gen var1=yw(year, week) if you already have the year and week variables created (which I currently do in SAS). I'm guessing this is also possible in SAS, but don't know the syntax. Thanks!

Re: Week/year formatting

LinusH — Wed, 06 Aug 2008 11:51:48 GMT

If you have year and week stored seperately in numeric columns, the syntax might look like this:

data step:

length yw $12;
yw=catx(' ',put(year,4.),"week",put(week,2.));

SQL:

select
catx(' ',put(year,4.),"week",put(week,2.)) as yw length =12
...

Regards,
Linus

Re: Week/year formatting

sbb — Wed, 06 Aug 2008 13:44:18 GMT

The most useful reply from Linus demonstrated the power of using SAS PROC FORMAT to tailor "formatted" date variables as needed for display purposes.

So, combining PROC FORMAT with a DATA step to assign a SAS character variable with the desired "yyyy WEEK ww" string, derived from a SAS DATE variable, use the code below.

PROC FORMAT ;
PICTURE yyyyww
LOW-HIGH='%0Y WEEK %0U' (DATATYPE=DATE);
RUN ;
data _null_;
retain year 2008 week 2;
* convert year and week to a SAS DATE var. ;
dt = intnx('week',mdy(1,1,year),week);
* now convert back to desired display format, as ;
* as SAS character variable. ;
dtx = put(dt,yyyyww.);
putlog dt= date9. dtx= ;
run;

Another possibility that does not involve a special user SAS format is to explore using one of the SAS formats WEEKU/WEEKV/WEEKW (check your ISO requirement for when WEEK=1 starts) which can generate a format "yyyyWwwdd" but with some truncation, shown below:

data _null_;
retain year 2008 week 2;
dt = intnx('week',mdy(1,1,year),week);
length dtx $12;
dtx = put(dt,weeku9.);
dtx = tranwrd(substr(dtx,1,7),'W',' WEEK ');
putlog dt= date9. dtx= ;
run;

Hope that helps.

Scott Barry
SBBWorks, Inc.

Re: Week/year formatting

deleted_user — Wed, 06 Aug 2008 13:54:21 GMT

Thanks for the input! Is there a way to create a year/week variable that is still a date (numeric)? Stata can create a numeric value notating year/week, similar to date values, that I think counts the number of weeks since Jan 1, 1960 (for example, 2008 week 8 has a value of 2503). Although creating a character variable would work, the historical data I'm working with (I am converting Stata code to SAS code) has the variable in the numeric format so that would be ideal.

Re: Week/year formatting

Olivier — Thu, 07 Aug 2008 07:42:21 GMT

I don't see any direct function to transform week and year into date, but there is the MDY function to create January 1st of a given year, and the INTNX function to "scroll" in time week by week.
[pre]
DATA _NULL_ ;
INPUT year week ;
myDate = INTNX("WEEK", MDY(1,1,year), week-1) ;
PUT year week myDate DDMMYY10. ;
CARDS ;
2008 2
2008 7
2008 35
2008 52
;
RUN ;
[/pre]
Is that what you were looking for ?
Olivier

Re: Week/year formatting

sbb — Thu, 07 Aug 2008 13:03:20 GMT

SAS stores an internal numeric value that represents a DATE (or DATETIME), based on a zero value being Jan 1 1960. How you choose to display the variable value externally is your choice. SAS has a set of FORMAT/INFORMAT called WEEKU, WEEKV, and WEEKW, used to input / output a SAS DATE variable in various year/week representations.

So, in fact, it is possible to have a year and week value, from which you can compute (and most importantly validate accuracy) a SAS DATE variable, and then display that date variable as you have requested.

Scott Barry
SBBWorks, Inc.