topic How to extract clean numerical data from a messy char variable? in SAS Programming

How to extract clean numerical data from a messy char variable?

xyxu — Sat, 10 Nov 2018 23:04:41 GMT

I have a $20 char variable that can take the following values:

data have;

input var $20.;

cards;

0.02

0.0125

0.05%

0.0100

0.5ABC

0.02or>5

run;

I want to keep only observations with purely numerical values: 0.02, 0.0125, 0.05%, 3, 0.0100. I also want these values to be formatted. How can I do this?

Re: How to extract clean numerical data from a messy char variable?

hashman — Sun, 11 Nov 2018 00:04:47 GMT

@xyxu:

From looking at your sample data, it appears that it can be as simple as using the COMMAw. informat:

data have ;                         
  input var $20. ;                  
  cards ;                           
0.02                                
0.0125                              
0.05%                               
3                                   
0.0100                              
0.5ABC                              
0.02or>5                            
run ;                               
                                    
data want ;                         
  set have ;                        
  value = input (var, ?? comma20.) ;
  if N (value) ;                    
run ;

However, be aware that COMMAw. merely strips the % sign (and also $ and some other characters) - it doesn't divide the result by 100. If that is what you want, you'd have to add more logic to handle it. Also, you aren't telling how you want to format the resulting variable, so I can't offer an advice on that head.

Paul D.

Re: How to extract clean numerical data from a messy char variable?

PGStats — Sun, 11 Nov 2018 03:45:18 GMT

Use informat percent20. instead. It also removes dollar signs, negates values in parentheses and divides by 100 only if a percent sign is present.

Re: How to extract clean numerical data from a messy char variable?

hashman — Sun, 11 Nov 2018 04:06:53 GMT

@PGStats:

An excellent suggestion if this is indeed what the OP wants.

Paul D.