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

[xyxu]

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

data have;

    input var $20.;

    cards;

0.02

0.0125

0.05%

3

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? 

[hashman]

@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. 

[PGStats]

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

[hashman]

@PGStats:

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

Paul D.