Hi,
I have a excel file like below:
ID wg wgunit hg hgunit
1 203 Lb 64.5 In (12 inches = 1 foot)
1 202.4 Lb 64.5 In (12 inches = 1 foot)
1 198 Lb 64 In (12 inches = 1 foot)
1 184 Lb 64 In (12 inches = 1 foot)
1 95 Kg 64 In (12 inches = 1 foot)
2 188 Lb 67 In (12 inches = 1 foot)
2 174.8 Lb 67 In (12 inches = 1 foot)
I want to make variable with same unit. Here is my syntax:
data a;
set b;
weightkg=wg;
if wgunit='lb' then weightkg=wg*0.4535924;
heightm=hg/100;
if hgunit='in' then heightm=hg*2.5/100;
run;
but output showed:
weightkg heightm
203.000 0.6450
202.400 0.6450
198.000 0.6400
184.000 0.6400
95.000 0.6400
188.000 0.6700
174.800 0.6700
I tried to debug my syntax, but it doesn't work.
Thanks for helping me.
I think you are trying something like this
data a;
set b;
if wgunit='Lb' then weightkg=wg*0.4535924;
else weightkg=wg;
if hgunit='In' then heightm=hg*2.5/100;
else heightm=hg/100;
run;
String comparison tests are case sensitive in SAS. "lb" is not equal to "Lb".
Never assume strings are always capitalized uniformly. Use the UPCASE function.
IF UPCASE(wgunit) = "LB" THEN weightkg=.....
conversely:
IF LOWCASE(wgunit)='lb' THEN weightkg=
I tried all versions of LCASE or UCASE, it showed same result. I am wondering why heightm=0.6 and etc. I need someone to help me figuring out the problem.
Thanks,
In SAS I believe the proper functions are upcase and lowcase function.
In a SQL query the upper and lower functions also work.
Duh, yeah. UPCASE. UCASE is for Visual Basic.
I think you are trying something like this
data a;
set b;
if wgunit='Lb' then weightkg=wg*0.4535924;
else weightkg=wg;
if hgunit='In' then heightm=hg*2.5/100;
else heightm=hg/100;
run;
Actually, if your data are EXACTLY as shown, I'd think you'd need something like the following (note: I modified your height calculation):
data b;
informat hgunit $23.;
input ID wg wgunit $ hg hgunit &;
cards;
1 203 Lb 64.5 In (12 inches = 1 foot)
1 202.4 Lb 64.5 In (12 inches = 1 foot)
1 198 Lb 64 In (12 inches = 1 foot)
1 184 Lb 64 In (12 inches = 1 foot)
1 95 Kg 64 In (12 inches = 1 foot)
2 188 Lb 67 In (12 inches = 1 foot)
2 174.8 Lb 162.56 Centimeters
;
data a;
set b;
weightkg=wg;
if wgunit='Lb' then weightkg=wg*0.4535924;
else weightkg=wg;
if hgunit="In (12 inches = 1 foot)" then heightm=hg/39.370;
else heightm=hg/100;
run;
Hi Stat@sas and Arthur,
Both ways worked very good and got same result. Many thanks for your expertise.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.