Wrong Week Numbers - Quick Demo
While working on data holding a record for each day, I wanted to create some graphs on week level. So I extracted the weeks with XDATE.WEEK but the week numbers returned by SPSS are nonsensical: every week starts on January 1 and most years end up with week 53 holding just 1 day.
There's different standards for week numbers but I think the very definition of a week is a 7 day time span. The following syntax demonstrates the problem.
SPSS Week Numbers Syntax Example
loop mydate = 1 to 500.
end input program.
*Convert mydate into actual date.
compute mydate = datesum(date.dmy(1,1,2013),mydate - 1,'days').
formats mydate (date11).
*Extract week and year from mydate.
compute week = xdate.week(mydate).
The result in data view may look normal at first. However, when we scroll down to case 365, we see that week 53 consists of 1 day. Like so, SPSS’ week numbers don't correspond to any conventional standard and can neither be converted into one.
Isoweeks in GoogleDocs
Interestingly, Google sheets has the
isoweeknum function returning the isoweeks I'm looking for. So a “workable solution” seemed to copy-paste these into an SPSS data file. Finally, MATCH FILES by date seemed to do the trick. And then I realized...
In the isoweek system, dates around new year’s can fall into a week from a different year. And unfortunately, GoogleDocs does not provide the years to which weeks belong. The screenshot below attempts to illustrate the problem.
Right. So extracting the year from December 30, 2013 obviously returns 2013. However, it falls in week 1, 2014. And neither SPSS nor GoogleDocs offers a function that'll insert 2014 into my dataset for this date.
Perhaps a bit of an anti climax but... no solution so far. I could go and look for a huge table holding a long date range and all isoweeks plus the years in which they fall. And convert it to SPSS. And merge it into several data files. But I'd much rather avoid such an ugly solution.
So... any suggestions anybody? Please drop me a comment below if you've a better idea.
Thanks for reading!