DATA Step, Macro, Functions and more

Invalid Numeric data on timepart

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Invalid Numeric data on timepart

[ Edited ]

I have a report that has a time value in excel formatted to MM: SS. (01:32; 00:56, etc)

 

When imported, they import as a text string. I need to convert it into a value that can be used in calculations. I've tried a couple of different methods to convert the text string. (Including changing the string to HH:MM: SS format) But every method I've tried to use to convert it returns null value and the Invalid numeric data, Time='00:26' error.

 

Can someone help me with the correct method to convert a time text string to a value that can be used for calculations?


Accepted Solutions
Solution
‎05-10-2016 03:18 PM
Super User
Posts: 5,513

Re: Invalid Numeric data on timepart

Posted in reply to EarlyCode

If you don't mind a message about conversion on the log, you could shorten your equations to:

 

myTime = 60* input(myTimeString, 2.) + scan(myTimeString, 2, ':');

View solution in original post


All Replies
Respected Advisor
Posts: 4,927

Re: Invalid Numeric data on timepart

Posted in reply to EarlyCode

myTime = input(myTimeString, time.);

PG
Contributor
Posts: 30

Re: Invalid Numeric data on timepart

Returns numerical values, that aren't in time format. If a second Format myTime Time.; is run it will show it in a time format, but the seconds become minutes. So 00:08 becomes 480 which SAS reads as 0:08:00 instead of 0:00:08.

A work around I've just come up with is
min = input(substr(myTimeString,1,2),2.);
sec = input(substr(myTimeString,4,2),2.);
myTime = (min*60) + sec;
FORMAT myTime Time.;

Which does convert 00:08 to 0:00:08. But seems like a lot of extra steps that I'm not sure if it's needed.
Respected Advisor
Posts: 4,927

Re: Invalid Numeric data on timepart

Posted in reply to EarlyCode

myTime = input(myTimeString, time.);

format myTime time5,;

PG
Contributor
Posts: 30

Re: Invalid Numeric data on timepart

Still converts seconds to minutes.
Solution
‎05-10-2016 03:18 PM
Super User
Posts: 5,513

Re: Invalid Numeric data on timepart

Posted in reply to EarlyCode

If you don't mind a message about conversion on the log, you could shorten your equations to:

 

myTime = 60* input(myTimeString, 2.) + scan(myTimeString, 2, ':');

Contributor
Posts: 30

Re: Invalid Numeric data on timepart

Posted in reply to Astounding
This works with fewer steps. Thank you,
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 304 views
  • 0 likes
  • 3 in conversation