SPSS TUTORIALS
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
input program.
loop mydate = 1 to 500.
end case.
end loop.
end file.
end input program.
execute.
*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).
execute.
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.
Solution
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!
THIS TUTORIAL HAS 11 COMMENTS:
By Andy W on November 24th, 2017
I typically do consistent weeks, e.g. Sunday-Saturday, because of this problem. The end week with less than 7 days you could normalize the metric to "per day", but pretty much everything to do with humans has pretty different patterns on the weekends, so it is still not comparable.
See https://dl.dropboxusercontent.com/s/i5cii0zdw78tszc/TimeExpand_Macros.sps?dl=0 for some macros to help create that weekly dataset setting from a consistent start date.
By Jon Peck on November 24th, 2017
Week numbers are nasty. There are at least three different schemes in use for determining them. Statistics simply counts from Jan 1. Since 7 doesn't evenly divide the number of days in a year, there is going to be a leftover part - week 53.
The ISO week number, quoting, is defined as
The ISO year consists of 52 or 53 full weeks, and where a week starts on a Monday and ends on a Sunday. The first week of an ISO year is the first (Gregorian) calendar week of a year containing a Thursday. This is called week number 1, and the ISO year of that Thursday is the same as its Gregorian year.
But, the ISO week number can actually reach back to the previous year, so for, say 2017,1,1, it gives 52 but for 2016.
To get the (nonISO) week number counting Sunday as the first day of the week, you can do this.
spssinc trans result=week
/formula "extendedTransforms.datetimetostr(value=bdate, pattern='%U')".
To start on Monday, change %U to %W.
For the ISO dates, a little more work is required. The code below returns the iso year, month, and date. The input is a regular SPSS date variable.
begin program.
import datetime
from spssdata import CvtSpssDatetime
def iso(thedate):
thedate = CvtSpssDatetime(thedate)
if not thedate is None:
return thedate.isocalendar()
else:
return Noneend program.
spssinc trans result = isoyear isomonth isoday
/formula "iso(bdate)".
By Jon Peck on November 24th, 2017
Note that the comment field lost the indentations in the begin program block :-( Trying again to indicate this.
import datetime
from spssdata import CvtSpssDatetime
def iso(thedate):
----thedate = CvtSpssDatetime(thedate)
----if not thedate is None:
--------return thedate.isocalendar()
----else:
--------return None
By Jon Peck on November 24th, 2017
Correction! the return values from the ISO example are
iso year, iso week number, iso weekday!
By Ruben Geert van den Berg on November 25th, 2017
Thanks Jon! I'll fix the indentation in the previous example.
I think this solution will work for me. However, isn't there a non Python solution as well? For some mysterious reason -and I really don't get this- I hear from many SPSS users that they don't have the Python Essentials. A wild guess is that this is related to older SPSS versions but I can't verify that.