SPSS Statistics

SPSS Statistics

Your hub for statistical analysis, data management, and data documentation. Connect, learn, and share with your peers! 

 View Only

  • 1.  Dates and times - conversion into properly formatted variables

    Posted Tue October 08, 2024 10:47 AM

    This is not a question, but rather an attempt to point at some nifty features of the built-in functions for dealing with dates and times. At least in my work, this is a very frequent source of difficulties.

    To be more specific, the way SPSS can convert dates formatted as text into working dates is quite fascinating. Almost any reasonable format can be handled. And it works even when the text is formatted differently within the same variable.

    I have put together a short example (where EXECUTE should be inserted after COMPUTE and IF commands if temporary stops are wanted):

    ***********************************************************.
    DATA LIST LIST(";")/date(A24) time(A20).
    BEGIN DATA
     November 13 2024  ;03:45 AM
    Oct 4   2024;    03:45PM
      Nov   14 2024 ;    11:15AM
    12   24    2024;    11:15 PM
    11/25/2024;4:56  PM
    END DATA.

    *This would of course be a silly way to set up the data, but the point is that many formats can be dealt with for date variables,
    *as long as the days, months and year details come in the order needed for a specific format.
    *Further, instead of the following COMPUTE command, the date format could be specified from the outset in the 
    *DATA LIST command: DATA LIST LIST/date(ADATE20) time(A20). Which also can be specified when importing text files through the menus.

    *Convert the text into working dates.
    COMPUTE date2=NUMBER(date,ADATE20).

    FORMATS date2(ADATE10).

    *Now to the times, a bit more cumbersome than the dates.
    *Some cleaning of the times, remove leading or trailing blanks.
    COMPUTE time=LTRIM(RTRIM(time)).

    *More cleaning, remove blanks. 
    COMPUTE time=REPLACE(time,' ','').

    *Make the time variable as small as possible.
    ALTER TYPE time(A=AMIN).

    *Insert a leading 0 when needed.
    COMPUTE time=CHAR.LPAD(time,7,'0').

    *Convert the times as strings to working time variables.
    IF CHAR.SUBSTR(time,6,2)='AM' time2=NUMBER(CHAR.SUBSTR(time,1,5),TIME5).
    IF CHAR.SUBSTR(time,6,2)='PM' time2=NUMBER(CHAR.SUBSTR(time,1,5),TIME5)+12*60*60.

    FORMATS time2(TIME5).

    COMPUTE datetime=date2+time2.
    EXECUTE.

    FORMATS datetime(DATETIME17).
    *************************************************.

    The date conversion, isn't it fascinating? How can such messy data be converted so easily?



    ------------------------------
    Robert Lundqvist
    ------------------------------


  • 2.  RE: Dates and times - conversion into properly formatted variables

    Posted Tue October 08, 2024 10:54 AM
    Thanks Robert for sharing. - Arthur



    Original Message


  • 3.  RE: Dates and times - conversion into properly formatted variables

    Posted Tue October 08, 2024 12:38 PM
    Thanks, Robert,

    I would add to this that the STATS TRANS extension command using the extendedTransforms module that is installed with that command provides two date functions that can read unusual date formats and create SPSS date variables or format date variables in other ways.  You specify a pattern described by using formatting characters.
    strtodatetime  (string to date/time) converts a string to a date, and datetimetostr does the opposite.

    The list of pattern characters is below.  This adds a lot of flexibility to date processing, although the built-in date functions in SPSS will handle most situations.

        %a Locale's abbreviated weekday name.  
        %A Locale's full weekday name.  
        %b Locale's abbreviated month name.  
        %B Locale's full month name.  
        %c Locale's appropriate date and time representation.  
        %d Day of the month as a decimal number [01,31].  
        %H Hour (24-hour clock) as a decimal number [00,23].  
        %I Hour (12-hour clock) as a decimal number [01,12].  
        %j Day of the year as a decimal number [001,366].  
        %m Month as a decimal number [01,12].  
        %M Minute as a decimal number [00,59].  
        %p Locale's equivalent of either AM or PM. (1)
        %S Second as a decimal number [00,61]. (2)
        %U Week number of the year (Sunday as the first day of the week) as a decimal number [00,53].
        All days in a new year preceding the first Sunday are considered to be in week 0. (3)
        %w Weekday as a decimal number [0(Sunday),6].  
        %W Week number of the year (Monday as the first day of the week) as a decimal number [00,53].
        All days in a new year preceding the first Monday are considered to be in week 0. (3)
        %x Locale's appropriate date representation.  
        %X Locale's appropriate time representation.  
        %y Year without century as a decimal number [00,99].  
        %Y Year with century as a decimal number.  
        %Z Time zone name (no characters if no time zone exists).  
        %% A literal "%" character. 




    --



    Original Message


  • 4.  RE: Dates and times - conversion into properly formatted variables

    Posted Wed October 09, 2024 02:39 AM

    After the previous posting, I realized that some of the readers perhaps primarily do not use syntax. The functions used above can however be used in the menu system as well. When you go to Transform/Compute Variables you have the "Numeric Expression" window. Enter the functions above there, for example "NUMBER(date,ADATE20)" (without the quotes) if "date", a string variable containing dates should be converted into a numeric variable.
    For those not using syntax yet, I also would like to suggest that you have a go at it.



    ------------------------------
    Robert Lundqvist
    ------------------------------

    Original Message