SAS Times not converting properly from string to time

[ColeG]

I have a series of date-time variables in the format

mm/dd/yyyy hhmm

I need to split these into date variables and time variables. The dates work okay, and sometimes the times work, but sometimes the time is slightly wrong.

Datetime examples:

10/26/2016 1400

10/26/2016 1826

1/29/2017 1004

If I use the code:

date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);

time1 = substr(datetime, max(1, length(datetime) - 4));
time = input(substr(time1, 1, length(time1) - 2) || ':' || substr(time1, length(time1) - 1), time5.);

Then I get the output

Date (#):            Time (#):

10/26/2016        14:00  -> right

10/26/2016        18:02  -> wrong

1/29/2017          10:00  -> wrong

However, if I don't try to make time a number and use the code:

date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);

time1 = substr(datetime, max(1, length(datetime) - 4));
time = substr(time1, 1, length(time1) - 2) || ':' || substr(time1, length(time1) - 1);

Then I get the output

Date (#):            Time (char):

10/26/2016        14:00  -> right

10/26/2016        18:26  -> right

1/29/2017          10:04  -> right

I need time to be numeric so that I can merge with other datasets that have time as a number. Any help would be appreciated.

[Kurt_Bremser · Accepted solution]

Use scan() for time1 also:

data test (keep=datetime date time);
input datetime $26.;
format date mmddyy10. time time5.;
date1 = scan(datetime, 1, ' ');
date = input(date1, mmddyy10.);
time1 = scan(datetime,2,' ');
time = input(substr(time1,1,2)!!':'!!substr(time1,3),time5.);
cards;
10/26/2016 1400
10/26/2016 1826
1/29/2017 1004
;
run;

[ColeG]

Ugh I knew it would be something simple. Thank you! Can you explain why scan works here but substring doesn't?

[Kurt_Bremser]

---

@ColeG wrote:

Ugh I knew it would be something simple. Thank you! Can you explain why scan works here but substring doesn't?

---

Your error was here:

time1 = substr(datetime, max(1, length(datetime) - 4));

Use

time1 = substr(datetime, max(1, length(datetime) - 3));

instead.

Your time1 always started with a blank, and had a length of 5.

That leading blank (which propagates through the rest of processing) seems to confuse the time5. informat, so that it starts reading the minutes from the 4th position (of a 6-byte string!), where the ':' is.

Confirm this by adding an additional line of input data:

10/26/2016 1430

You'll get a time of 14:03 with your original code.

That's why I prefer to use scan() instead of substr()s. Has less potential for such errors.