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!
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.
input charwt :$10.;
if index(charwt,'lbs')>0 then do;
pounds = input(scan(charwt,1),3.);
oz = input(scan(charwt,2),3.);
else kg = input(compress(charwt,,'l'),4.);
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.