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.
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" ;
First set of questions: What are you doing with data? How do you plan to manipulate it?
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.
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" ;
Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.