SAS is the leader in analytics. Through innovative Analytics, Artificial Intelligence and Data Management software and services, SAS helps turn your data into better decisions. Read the PDF file into SAS and then extract each relevant text and attribute as a string. Separate text and attributes from the string. Obtain row and column number based on the text and their attributes. Re-construct text from data set when the text.
- I have a given column (CV that I want to test in a ANCOVA) in my data set which contains numbers similar to this “-,85351” and its structure is character by default. Now nothing has worked to convert this into numeric. It either got changed into NAs or a whole lot of different numbers.
- In this article, we will convert text to number in multiple versions of SQL Server and will see the difference. I will use four different Data conversion functions (Convert, Cast, TryConvert & TryCast) to convert Text to Number. Let me explain this with simple examples. In SQL Server 2005/2008: Example 1: (Convert Text.
Since we added the new 'Recommended by SAS' widget in the SAS Support Communities, I often find myself diverted to topics that I probably would not have found otherwise. This is how I landed on this question and solution from 2011 -- 'How to convert 5ft. 4in. (Char) into inches (Num)'. While the question was deftly answered by my friend (and SAS partner) Jan K. from the Netherlands, the topic inspired me to take it a step further here.
Jan began his response by throwing a little shade on the USA:
Sas Convert To Number
Short of moving to a country that has a decent metric system in place, I suggest using a regular expression.
On behalf of my nation I just want say that, for the record, we tried. But we did not get very far with our metrication, so we are stuck with the imperial system for most non-scientific endeavors.
Matching patterns with a regular expression
Regular expressions are a powerful method for finding specific patterns in text. The syntax of regular expressions can be a challenge for those getting started, but once you've solved a few pattern-recognition problems by using them, you'll never go back to your old methods.
Beginning with the solution offered by Jan, I extended this program to read in a 'ft. in.' measurement, convert to the component number values, express the total value in inches, and then convert the measurement to centimeters. I know that even with my changes, we can think of patterns that might not be matched. But allow me to describe the updates:
- Long-time users of PRXPARSE in SAS will notice that I did not apply the common practice of wrapping PRXPARSE in an 'IF _N_=1' condition. For several releases of SAS, it's been the case that when using a constant parse pattern the DATA step will compile the regular expression just once. Parsing the expression hundreds or thousands of times across each DATA step iteration would be a performance concern -- but since in our case the pattern never changes, SAS compiles it just once.
- The PRXPOSN function returns the nth 'capture buffer' from the pattern match. Capture buffers are identified in the pattern by parentheses -- you count each open parenthesis to arrive at the expected buffer. So the first buffer matches on the first sequence of digits: (d*). The second buffer is the optional whitespace between ft. and in.: (s*). Buffer 3 is the entire pattern for 'n in.': ((d?.?d?)in.). Finally, buffer 4 is an 'inner' capture group of buffer 3, containing just the sequence of digits with optional decimal for inches: (d?.?d?)
- The PRXPOSN function returns the text value of the match, so we have to use the INPUT function to convert that to a SAS numeric value.
- Finally, I added the calculations to convert to total inches, and then centimeters.
Here's my program, followed by the result:
Other tools to help with regular expressions
The Internet offers a plethora of online tools to help developers build and test regular expression syntax. Here's a screenshot from RegExr.com, which I used to test some aspects of my program.
Tools like these provide wonderful insight into the capture groups and regex directives that will influence the pattern matching. They are part tutorial, part workbench for regex crafters at all levels.
Many of these tools also include community contributions for matching common patterns. For example, many of us often need to match/parse data with e-mail addresses, phone numbers, and other tokens as data fields. Sites like RegExr.com include the syntax for many of these common patterns that you can copy and use in your SAS programs.
See Also
Convert Text To Number In Sas
- In his book High-Performance SAS Coding, Christian Graffeuille (ChrisNZ on the communities) devotes a chapter to using regular expressions in SAS. (He also answers a TON of regex questions on the community.)
- Ron Cody also teaches about regular expressions for data cleansing -- see his blog post and even more in his book, Cody's Data Cleansing Techniques using SAS.