Hi SPSS Users,
I've been struggling for a few days with my perfectionist personality and a warning code. What I have done works, but it triggers warnings that I'd like to get rid of if I can.
I'm cleaning data and my data comes with the dates in the atypical format of YYYYMMDD. As this is not a a typical date format, to get it into a date, I am first making it a string, then calling sub-strings to reformat it into a date I can work with.
ALTER TYPE EligibilityDate(A8).
EXECUTE.
COMPUTE mo = number (CHAR.SUBSTR (EligibilityDate, 5, 2), F2).
COMPUTE da = number (CHAR.SUBSTR (EligibilityDate, 7, 2), F2).
COMPUTE yr = number (CHAR.SUBSTR (EligibilityDate, 1, 4), F4).
COMPUTE EligibilityDate2 = $SYSMIS.
COMPUTE EligibilityDate2 = DATE.MDY (mo, da, yr).
EXECUTE.
FORMATS EligibilityDate2(ADATE10).
EXECUTE.
Running this code results in 0 missing values for the string (EligibilityDate) and then after a slew of #635 warnings for each line that originally was missing, I get the correct set of missing values for EligibilityDate2. So, in essence my code is doing what I want it to do. However, I'd like to clean it up and get rid of the errors if I can figure out how.
First, I tried coding the blank strings to missing by adding Missing Values EligibilityDate (''), this at least has the same Missing values for EligibilityDate after being converted to a string as it had when it was a number. Unfortunately, once that second execute command after all the compute lines runs, I get all those #635 Warnings again.
ALTER TYPE EligibilityDate(A8).
MISSING VALUES EligibilityDate ('').
EXECUTE.
COMPUTE mo = number (CHAR.SUBSTR (EligibilityDate, 5, 2), F2).
COMPUTE da = number (CHAR.SUBSTR (EligibilityDate, 7, 2), F2).
COMPUTE yr = number (CHAR.SUBSTR (EligibilityDate, 1, 4), F4).
COMPUTE EligibilityDate2 = $SYSMIS.
COMPUTE EligibilityDate2 = DATE.MDY (mo, da, yr).
EXECUTE.
FORMATS EligibilityDate2(ADATE10).
EXECUTE.
I next suspected that it had to do with not previously defining mo, day and yr as system missing, so I made those definitions first. Unfortunately, the COMPUTE mo = number (CHAR.SUBSTR... overwrites that and the same problem happens at the same point in the code.
ALTER TYPE EligibilityDate(A8).
MISSING VALUES EligibilityDate ('').
EXECUTE.
COMPUTE mo = $SYSMIS.
COMPUTE da = $SYSMIS.
COMPUTE yr = $SYSMIS.
EXECUTE.
COMPUTE mo = number (CHAR.SUBSTR (EligibilityDate, 5, 2), F2).
COMPUTE da = number (CHAR.SUBSTR (EligibilityDate, 7, 2), F2).
COMPUTE yr = number (CHAR.SUBSTR (EligibilityDate, 1, 4), F4).
COMPUTE EligibilityDate2 = $SYSMIS.
COMPUTE EligibilityDate2 = DATE.MDY (mo, da, yr).
MISSING VALUES EligibilityDate2 ('').
EXECUTE.
FORMATS EligibilityDate2(ADATE10).
EXECUTE.
What ideas do you have to clean this up?
In case you aren't familiar with Warning #635, or want the explicit language:
>Warning # 635
>The string to be converted via the NUMBER function is of zero length. The
>result has been set to the system-missing value.
Not an SPSS user, but this problem is common in SAS as well. You need a conditional statement (If ... Then ...) to test for missing, and avoid doing the conversion on missing values.
Also, I would be surprised if SPSS doesn't have a way to handle YYYYMMDD dates formats directly.
You can ask the question on YouTube for you to see the steps to follow.
- Badges
- Science topic
- Similar topics
- Mathematical Sciences
- Statistics
- Statistical Software
- SPSS