SAS Times not converting properly from string to time

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

SAS Times not converting properly from string to time

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.


Accepted Solutions
Solution
‎06-16-2017 09:50 AM
Super User
Posts: 7,465

Re: SAS Times not converting properly from string to time

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎06-16-2017 09:50 AM
Super User
Posts: 7,465

Re: SAS Times not converting properly from string to time

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

Re: SAS Times not converting properly from string to time

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

Super User
Posts: 7,465

Re: SAS Times not converting properly from string to time


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 122 views
  • 0 likes
  • 2 in conversation