BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xinjian
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

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;

View solution in original post

7 REPLIES 7
rickpaulos
Obsidian | Level 7

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=

xinjian
Calcite | Level 5

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,

Reeza
Super User

In SAS I believe the proper functions are upcase and lowcase function.

In a SQL query the upper and lower functions also work. 

rickpaulos
Obsidian | Level 7

Duh, yeah. UPCASE.  UCASE is for Visual Basic.

stat_sas
Ammonite | Level 13

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;

art297
Opal | Level 21

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;

xinjian
Calcite | Level 5


Hi Stat@sas and Arthur,

Both ways worked very good and got same result. Many thanks for your expertise.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 998 views
  • 0 likes
  • 5 in conversation