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 Jon Peck on November 25th, 2017
You could code the iso week algorithm following the description here, https://en.wikipedia.org/wiki/ISO_week_date,
but it would be painful. You could also request a new function for Statistics from the offering manager.
As for Python, it has been a standard part of the Statistics install since version 21 or 22, although users can refuse it at install time. I suspect lots of users have it but don't know it.
Of course, for statistical purposes, iso weeks don't work very well, because of the unequal number of days as well as seasonal issues.
By Robert on April 17th, 2020
How about the following:
DATA LIST LIST/date(SDATE8).
BEGIN DATA
20191231
20200103
20200407
20201215
END DATA.
DATASET NAME weeknr WINDOW=FRONT.
FORMATS date(SDATE10).
COMPUTE #date2=date.
COMPUTE week=NUMBER(CHAR.SUBSTR(STRING(#date2,WKYR8),1,2),F2).
IF week=53 week=1.
EXECUTE.
FORMATS week(F2).
By Ruben Geert van den Berg on April 18th, 2020
Hi Robert, thanks for your suggestion!
However, it doesn't work: week 53 is always followed by week 1. Converting week 53 into week 1 results in week 1 having more than 7 days.
The basic problem is that WKYR8 uses the same wrong "week algorithm" as XDATE.WEEK.
Throwing in just a handful of dates is not a good strategy for testing either: if you run the syntax on a 10-year date span, you're much more likely to detect problems. For example, 6 January 2013 was the first day of week 2, 2013. None of the proposed solutions returns that correctly.
*Create 500 empty cases.
input program.
loop mydate = 1 to 5000.
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).
*Test week number extraction.
COMPUTE week=NUMBER(CHAR.SUBSTR(STRING(mydate,WKYR8),1,2),F2).
IF week=53 week=1.
EXECUTE.
By Lars NP on March 5th, 2021
I have used the Python script described by Jon Peck in an earlier comment for a few years, but after the last update to SPSS 27, Python 2 is no longer supported. When I try running my old syntax I get a warning to update my script to Python 3.8.x distribution. I have tried various automated converters, but haven't managed to convert the code and I don't have the user experience with Python to do it manually. Could anyone help?
My current Python 2 code using "DateVariable" as basis is;
begin program.
import datetime
from spssdata import CvtSpssDatetime
def iso(spssdate):
return CvtSpssDatetime(spssdate).isocalendar()
end program.
spssinc trans result=ISOyear ISOweek ISOday TYPE = 0
/formula "iso(DateVariable)".
By Ruben Geert van den Berg on March 5th, 2021
Hi Lars!
Try this:
input program.
loop id = 0 to 1000.
compute mydate = datesum(date.dmy(1,1,2019),id,'days').
end case.
end loop.
end file.
end input program.
execute.
formats mydate(date11).
begin program python3.
import datetime
from spssdata import CvtSpssDatetime
def iso(spssdate):
return CvtSpssDatetime(spssdate).isocalendar()
end program.
spssinc trans result=ISOyear ISOweek ISOday TYPE = 0
/formula "iso(mydate)".
Hope that helps!
SPSS tutorials