Data exported from Google Analytics may use a nicely readable date format (such as "Sunday, January 30, 2006"). This format, however, is not suitable for time calculations. This tutorial shows how to convert it into a proper SPSS Date Variable.
The syntax below shows how to convert a date variable from Google Analytics into an SPSS date variable suitable for time calculations.
SPSS Syntax Example
data list free/Day(a30).
'Sunday, January 30, 2006 '
'Sunday, February 3, 2006 '
'Tuesday, March 19, 2006 '
'Tuesday, April 4, 2006 '
'Tuesday, May 24, 2006 '
'Tuesday, June 10, 2006 '
'Tuesday, July 29, 2006 '
'Tuesday, August 2, 2006 '
'Tuesday, September 22, 2006 '
'Tuesday, October 7, 2006 '
'Tuesday, November 13, 2006 '
'Tuesday, December 31, 2006 '
*2. Convert Google Analytics date to SPSS date.
compute date = substr(day,index(day,',') + 2).
compute date = replace(date,', ','/').
do repeat a = 1 to 12 / b = 'Jan','F','Mar','Ap','May','Jun','Jul','Au','S','O','N','D'.
if substr(date,1,len(b)) = b date = concat(string(a,f2),'/',substr(date,index(date,' ') + 1)).
alter type date(adate10).
*3. Example of time calculation.
compute days_between_date_and_now = datediff($time,date,'days').
- The conversion basically relies on the
- First, however, the original string format has to be adjusted. We'll first remove the names of the days which comes down to removing the first characters through the first "
,". We substitute INDEX in SUBSTR for accomplishing this.
- Next, we'll replace "
," (mind the space) by "
," by using the REPLACE function.
- In ALTER TYPE it's important to use
ADATE10. This ensures that the first two digits are used for the month (and not for the day as with