Changing data to the same format

abrice520 — Sun, 25 Apr 2021 04:26:53 GMT

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!

Re: Changing data to the same format

Patrick — Sun, 25 Apr 2021 06:08:04 GMT

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;

topic Changing data to the same format in SAS Programming

Changing data to the same format

Re: Changing data to the same format