converting multiple character variables with different formats to a SAS time variable

[mrprerost]

I have a SAS dataset with three date variables (all character, $10) that I am trying to convert to SAS date variables using SAS 9.4. The first two (initialvisitdate and birthdate) are in the MM/DD/YY character format and the third (conceptiondate) has some observations in the MM/DD/YY format and some in the MM/DD/YYYY format. This is the code that I have been trying (with various date formats in place of 'anydtdte10.'):

data test;
set xxxx.xxxxxxxxxx;

inivisitdt = INPUT(initialvisitdate, anydtdte10.);  /*  I have tried MMDDYY8.  MMDDYY. etc   */
birthdt = INPUT(birthdate, anydtdte10.);
conceivedt = INPUT(conceptiondate, anydtdte10.);

format inivisitdt birthdt conceivedt date9.;

run;

The 'conceptiondate' variable converts correctly from the 2 character formats described above, but only one observation for the initialvisitdate converted (observation 120 out of 289) - the rest are missing (.) and only 2 observations for the birthdate converted. There doesn't appear to be anything different for these 3 observations. There are no error or warning codes in the log. How can I convert all three of these variables?

[ChrisNZ]

> There doesn't appear to be anything different 

If the result is different then the input is different.

You have to find the difference to fix the problem.

Maybe some non printable characters such as tab? 

The can be spotted using the notprint() function.

Another way to see rogue characters is to print the string using the $hex. format and look for values outside of the 2F-39 range (and 20 for space).

[Tom]

If you know the values are in MDY order why not use MMDDYY informat?

Are you sure your values don't have leading spaces?  SAS will not normally display those in the log but they can really mess up the INPUT() function.

data test;
  input str $char15.;
  date=input(str,mmddyy10.);
  format str $quote. date mmddyy10.;
cards;
1/3/19
10/23/2018
 10/23/2019
  10/23/2019
   10/23/2019
    10/23/2019
     10/23/2019
;

Result

Obs    str                        date

 1     "1/3/19"             01/03/2019
 2     "10/23/2018"         10/23/2018
 3     " 10/23/2019"                 .
 4     "  10/23/2019"       10/23/2020
 5     "   10/23/2019"      10/23/2002
 6     "    10/23/2019"              .
 7     "     10/23/2019"             .

[ballardw]

---

@Tom wrote:

If you know the values are in MDY order why not use MMDDYY informat?

Are you sure your values don't have leading spaces?  SAS will not normally display those in the log but they can really mess up the INPUT() function.

data test;
  input str $char15.;
  date=input(str,mmddyy10.);
  format str $quote. date mmddyy10.;
cards;
1/3/19
10/23/2018
 10/23/2019
  10/23/2019
   10/23/2019
    10/23/2019
     10/23/2019
;

Result

Obs    str                        date

 1     "1/3/19"             01/03/2019
 2     "10/23/2018"         10/23/2018
 3     " 10/23/2019"                 .
 4     "  10/23/2019"       10/23/2020
 5     "   10/23/2019"      10/23/2002
 6     "    10/23/2019"              .
 7     "     10/23/2019"             .

---

But are easily fixed if leading:

data test;
  input str $char15.;
  date=input(left(str),mmddyy10.);
  format str $quote. date mmddyy10.;
cards;
1/3/19
10-23-2018
 10.23.2019
  10/23/2019
   10/23/2019
    10/23/2019
     10/23/2019
;

Note that the format will also use a number of other delimiters than / just fine.