Manipulating Variables in the Same Column With Both Character and Numerical Values

[madtex99]

Hi, I am on SAS 9.4 and I need to convert weight variables in a column to pounds if they are in kilograms and do nothing if the value is in pounds. Some variables read 7lbs12oz and others read 3.4kg. I don't know how to ignore or remove the alphabetical characters so that I can cleanly do the math on the kilogram values and leave the rest alone. Does that make sense? Please help!

[ballardw]

Note even worth the effort to fix the existing variable. Create a new variable that is numeric and apply the conversion so the value is in the desired units. Also once a variable is character in SAS it will stay such. So just create new variable (and drop the old one if not needed).

This shows one way to extract the different values that you show.

data have;
   input charwt :$10.;
datalines;
 7lbs12oz
 3.4kg
 ;

 data want;
    set have;
    if index(charwt,'lbs')>0 then do;
      charwt=compbl(translate(charwt,'     ','lbsoz'));
      pounds = input(scan(charwt,1),3.);
      oz     = input(scan(charwt,2),3.);
    end;
    else kg = input(compress(charwt,,'l'),4.);
run;

Translate replaces characters in a second list with the matching position in the first, so the above replaces all the letters shown with blanks. The Compbl function compresses multiple blanks down to a single blank. Scan pulls values separated by default characters, in this case the blank, and inputs the values into numeric pound and ounce values.

The KG example uses compress to remove all lowercase letters and inputs the remaining string. We couldn't use Compress with the pounds and ounces because if you have 1lbs11oz and 11lbs1oz they would both look like 111 after removing all the letters.

After you are happy the result you could DROP any of the unneeded variables.

Then send a nasty gram to who ever provided this data. This is like 1960 "data" entry and should not be tolerated anywhere.