changing ' and " to a numeric variable to calculate total inches = it's a text variable

[mgrzyb]

Hi. I need some help. I saw another ? that was similar, but it did not work.  My goal is to convert a text variable, such as 6'4" into inches (then I can change it to the metric system).

The data looks like this - it's a text var. 

ht

5'  6"

5'11"

6' 2"

etc.   they have different spacings, too. 

If anyone could help me, please do so.  There are different widths between the feet and inches as well. 

Thanks. Mava

[satya7777]

Please try the following code. It may meet your requirement.

data test;
input ht $10.;
datalines;
5'6"
5'11"
6'2"
;
run;

data test2(drop=t1);
set test;
feet=input(compress(scan(ht,1,"'")),3.);
t1=index(ht,"'");
inches=input(compress(scan(substr(ht,t1+1),1,'"')),3.);
run;

The output contains feet and inches values in separate variables. Perform numeric operation and add those values as per your requirement.

Thanks & Regards,

Satya

[mgrzyb]

Satya,

Thank you! It works. But it's in a permanent dataset.  

So how do I handle that?  I have tons of variables and don't know the spacing. 

I would like to know how to handle that for a huge database.

This works when I copy the data for a separate database = I can always merge it by ID, but there has to be a way to do this using a permanent database. 

Any suggestions? 

[mgrzyb]

This is the code I have - it's a permanent database. 

Sayta's pgm helped, but I can't figure it out for a perm. database.  It doesn't work unless I copy thousands of ID vars and ht variable. 

My variable for ht is named ht2mg.

I tried this:

data a;
set aung.try_ht2;
run;

data b(drop=ht2mg);
set a;
feet=input(compress(scan(ht2mg,1,"'")),3.);
ht2mg=index(ht2mg,"'");
inches=input(compress(scan(substr(ht2mg,t1+1),1,'"')),3.);
run;

proc print data=b; run;

[andreas_lds]

Sorry, can't understand your problem.

You have a dataset named aung.try_ht2 having a variable named ht2mg, containing something in feet and inches (the example data you posted).  The code you posted should give you the required result. What do you want as output?

EDIT:

Now i see, you made a small change to the code provided by @satya7777, you have replaced "t1" with "ht2mg". The log should contain some notes about missing values.

data b(drop=ht2mg);
  set aung.try_ht2;
  feet=input(compress(scan(ht2mg,1,"'")),3.);
  t1=index(ht2mg,"'"); /* was: ht2mg = index.... */
  inches=input(compress(scan(substr(ht2mg,t1+1),1,'"')),3.);
run;

Another way to extract numbers from strings is using regular expressions:

data want;
   set test;

   length feet inches 8 rx 8;
   retain rx;

   if _n_ = 1 then do;
      rx = prxparse(cats("/^(\d+)'", '\s*(\d+)"\s*/'));
   end;
   
   if prxmatch(rx, trim(ht)) then do;
      feet = input(prxposn(rx, 1, trim(ht)), best.);
      inches = input(prxposn(rx, 2, trim(ht)), best.);;
   end;

run;

[mgrzyb]

the feet in a number and inches in a number.  I got errors.  So I tried this:

ODS HTML close; ODS listing ;
data a (keep= id mrn ht2mg ) out=b;
set aung.try_ht2;
run;
proc print data=b noobs; run;

data c (drop=t1);
set b;

feet=input(compress(scan(ht2mg,1,"'")),3.);
t1=index(ht2mg,"'");
inches=(compress(scan(substr(ht2mg,t1+1),1,'"')),3.);
run;

proc print data=c; run;

I don't understand what the t1 means.   I do thank you for your code, but I have thousands to do.  I can separate the id and mrn with ht2mg (text var) into a separate database, but is there an easier way?  The code above did not work. 

 I got these errors:

250
251
252 data c (drop=t1);
253 set b;
254
255 feet=input(compress(scan(ht2mg,1,"'")),3.);
256 t1=index(ht2mg,"'");
257 inches=(compress(scan(substr(ht2mg,t1+1),1,'"')),3.);
-
22
200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

258 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
257:9
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.C may be incomplete. When this step was stopped there were 0 observations and 5 variables.
WARNING: Data set WORK.C was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

thank you. 

[mgrzyb]

ODS HTML close; ODS listing ;
data a (keep= id mrn ht2mg) ;
set aung.try_ht2;
run;

data b (drop=t1);
set a;

feet=input(compress(scan(ht2mg,1,"'")),3.);
t1=index(ht2mg,"'");
inches=(compress(scan(substr(ht2mg,t1+1),1,'"')),3.);
run;

proc print data=b; run;

My log says this:

326
327 data b (drop=t1);
328 set a;
329
330 feet=input(compress(scan(ht2mg,1,"'")),3.);
331 t1=index(ht2mg,"'");
332 inches=(compress(scan(substr(ht2mg,t1+1),1,'"')),3.);
-
22
200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT,
IN, LE, LT, MAX, MIN, NE, NG, NL, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

333 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
332:9
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.B may be incomplete. When this step was stopped there were 0 observations and 5 variables.
WARNING: Data set WORK.B was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

[andreas_lds]

"t1" is just a variable containing the position of the first ' in ht2mg.

Please post code and log always using {i} or running man icon, to maintain readability.

Still confusing: "I do thank you for your code, but I have thousands to do" - hardly able to understand what you mean.

Can you post an extract of your data as data-step, so that we can see what you really have?

[mgrzyb]

Sure …. give a minute to get rid of the personal information.  

When I said, "thanks," I meant that it worked if I just typed out those heights.  I have a huge dataset of about 2 thousand people.  I don't have the time to retype it all for an input statement.  

Thanks.

[mgrzyb]

I have attached an abbreviated perm data set which includes ht2mg.  In the big data set, I have many more variables not in this order, but I can reorder it.  

Thanks.  I can't seem to attach the perm. data set. It won't accept it.     I tried copying it in word, a sas perm data set, but the attachment part will not allow me to attach it!!

[mgrzyb]

Here is my code:

data a;
set aung.partial_dset_mg ;
run;

data test2(drop=t1);
set a;
feet=input(compress(scan(ht,1,"'")),3.);
t1=index(ht,"'");
inches=input(compress(scan(substr(ht,t1+1),1,'"')),3.);
run;

proc print data=test2;
run;

Please help me.  I need this for future use as well. Thanks. 

[mgrzyb · Accepted solution]

Hey!

I GOT IT!!!!!

Yes!  Thank you all!!!! 

I figured it out.  Just had to step away from the computer for a while.  

[ChrisNZ]

Please accept the post that helped you as the solution rather than your own "it works now" post.