8414_en-US

liliconcom

How to extract only numeric values out of text string<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

I have a large data table which has a  column containing char text.    An example of the text  description is  listed below.

I need to do some descriptive statistics on the duration to open/close for the robot. 

I would like to extract the numeric portion of the character string and put it in a new column with numeric data type.

Is there a formula function I could use to do that  on a brand new column ?

Sample text

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.530000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.030000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.010000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.510000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "2.955000" Secs

Re: How to extract only numeric values out of text string

Hi JayV.,

The Word() function will also take an argument to specify the delimiters, so you can avoid the Substr() part of your formula and just use:

   Num( Word( 11, :Text, "\!" " ) )

The \! is the escape string to tell the parser that the " is part of the quoted string.

-Jeff

Re: How to extract only numeric values out of text string

Thank you

Re: How to extract only numeric values out of text string

Depends upon EXACTLY what you want.  Does the following come close?

data have;

   input string & $100.;

   cards;

Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.530000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.030000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.010000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.510000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "2.215000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.160000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs

: Valve Movement to "CLOSE" position Completed. Time Taken for Movement "1.815000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "3.140000" Secs

: Valve Movement to "OPEN" position Completed. Time Taken for Movement "2.955000" Secs

;

data want (drop=string both);

  set have;

  both=compress(substr(string,anydigit(string)),'"');

  num=input(substr(both,1,anyalpha(both)-1),12.);

  unit=substr(both,anyalpha(both));

run;