topic Re: SAS Times not converting properly from string to time in SAS Programming

SAS Times not converting properly from string to time

ColeG — Fri, 16 Jun 2017 13:33:16 GMT

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.

Re: SAS Times not converting properly from string to time

Kurt_Bremser — Fri, 16 Jun 2017 13:40:42 GMT

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;

Re: SAS Times not converting properly from string to time

ColeG — Fri, 16 Jun 2017 13:45:04 GMT

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

Re: SAS Times not converting properly from string to time

Kurt_Bremser — Fri, 16 Jun 2017 14:11:10 GMT

@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.