Subscribe Bookmark RSS Feed

How to extract only numeric values out of text string

jayv_

Community Trekker

Joined:

Apr 17, 2014

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

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

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

-Jeff
5 REPLIES

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;

jayv_

Community Trekker

Joined:

Apr 17, 2014

Arthur,

     Thank you for your reply.   I started reading the Char functions help manual.

     I applied the following formula on my text column  which seemed to work.

     Num(Substr(Word(12, :Text), 2, 8))

     a) Extract  12th word from Text string   (i.e. "1.530000")

     b) Extract substring between quotations  (i.e. 1.530000)

     c) Convert character to numeric using Num function  (i.e. 1.53)

reeza

Community Trekker

Joined:

Jun 23, 2011

Try the scan function with the " as your delimiter.

scan(string, 4, '"')

Solution

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

-Jeff
jayv_

Community Trekker

Joined:

Apr 17, 2014

Your solution worked perfectly also.  

I learnt something new with regards to escape string/delimiters. Thanks !