String or Scan Function to solve height format

[Denali]

Hi,

I imported a dataset from Excel and height variable contains the symbols for feet and inches. Currently SAS read it as Char. Could you please advise me how to clean up this format issue? Or how do use the string or scan function?

height
5' 2.795"
5' 1.614"
4' .5"
5' 2.402"
3' 7.78"
3' 3.764"
5' 7"
5' 2.52"
5' 4.5"
5' 4"
5' 6.378"
5' 3"
5' 7.323"
4' .622"
5' 8"
5' 10.25"
5' 5.118"
5' 6.732"
3' 11.441"
4' 9"
4' 11.449"
3' 10"
4' 9.323"
4' 11.5"
4' 5.15"
3' 5.929"
4' 1.409"
5' 3.5"

Thank you.

[PabloNogueras]

First set of questions:  What are you doing with data?  How do you plan to manipulate it?

[mkeintz]

You could construct an informat that uses regular expressions as in:

proc format;
  invalue inft  (default=2)  "s/'//" (regexpe) = _same_ other=_same_;
  invalue inin(default=6) 's/"//' (regexpe) = _same_ other=_same_; 
run;

data want;
  input  ft  inft2. inch :inin.;
  total_height=12*ft+inch;
  if _n_=1 then put (_all_) (=);
datalines;
5' 2.795"
5' 1.614"
4' .5"
5' 2.402"
3' 7.78"
3' 3.764"
5' 7"
5' 2.52"
5' 4.5"
5' 4"
5' 6.378"
5' 3"
5' 7.323"
4' .622"
5' 8"
5' 10.25"
5' 5.118"
5' 6.732"
3' 11.441"
4' 9"
4' 11.449"
3' 10"
4' 9.323"
4' 11.5"
4' 5.15"
3' 5.929"
4' 1.409"
5' 3.5"
run; 

I suppose one could construct a regular expression that simultaneously read in the feet and inches and calculate a correct height in either inches or feet, but this is as far as I would go.

[ballardw · Accepted solution]

If you are looking to get feet and inches as numeric values here is one way:

data example;
   input str $ 1-9;
   feet = input(scan(str,1,"'"),f1.);
   inches= input(scan(str,2," '"""),best.);

datalines;
5' 2.795"
5' 1.614"
4' .5"
5' 2.402"
3' 7.78"
3' 3.764"
5' 7"
5' 2.52"
5' 4.5"
5' 4"
5' 6.378"
5' 3"
5' 7.323"
;

[Denali]

Thank you so much!