Changing data to the same format

[abrice520]

I'm trying to take maternal height in feet and inches and convert it to just inches. However, I've noticed that some of the data is entered in this format: 05:03 (for 5ft 3 inches) while other entries exclude the colon, so 0503. I'm looking for a way to either add the colon into those that have it missing or remove the colon, so that the data looks the same. Here is the code I was using before I realized this issue:

MHInch= scan(MotherHeight,1,":")*12 + scan(MotherHeight,2,":")*1;

Thanks!

[Patrick]

As long as you can rely that your source string is always 4 characters after removal of the column below should work.

If you can't be sure then please provide representative "have" sample data with all the potential patterns the code needs to work for.

data have;
  input MotherHeight $;
  MHInch=sum( 
              input(substr(MotherHeight,1,2),best32.)*12 
              ,input(substr(compress(MotherHeight,':'),3,2),best32.)
            );
datalines;
05:03
0503
;

Code like below could help you figure out what patterns you're dealing with.

proc sql;
  select distinct 
    prxchange('s/\d/9/',-1,strip(MotherHeight)) as pattern
  from have
  ;
quit;