## 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

***Create 500 empty cases.**

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 8 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.