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

Hello, I use the WHERE to set my data, but the log shows the error like below:

And I'm wondering what's goes wrong.

Thank you. 

proc import datafile='/home/u63518324/sasuser.v94/Epi_test.xls'
out=homework_per
dbms=xls;
getnames=yes;
run;

data homework_1;set homework_per;
BMI=weight/(height*height)*10000;
if BMI<24 then BMI_group=1;
else if BMI>=24 then BMI_group=2;
run;

proc format;
value BMI_group 1='normal weight' 2='overweight';
run;

data homework_1;set homework_1;
format BMI_group BMI_group.;
run;

proc print data=homework_1 AND BMI_group='overweight';
where sex='F';
run;

proc print data=homework_1;
where BMI_group='overweight';
run;
ERROR: WHERE clause operator requires compatible variables.
1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

You need to amend your data step so variable bmi_group gets populated.

data homework_2;
  set homework_1;
  bmi_group=put(bmi,bmi_group.);
run;

For just using a formatted value in a where clause you could also directly write code like:

proc print data=homework_1;
  where put(bmi,BMI_group.)='overweight';
run;

And if you then want to print the bmi values with a format applied you could code like:

proc print data=homework_1;
  where put(bmi,BMI_group.)='overweight';
  format bmi bmi_group.;
run;

 

View solution in original post

4 REPLIES 4
andreas_lds
Jade | Level 19

Assuming that "and BMI_group ..." belongs to the where statement.

proc print data=homework_1;
where sex='F' AND BMI_group='overweight';
run;

Attaching a format to a variable does not change the type of the variable, only the way its values are displayed changes. So BMI_group is still numeric and the comparison with "overweight" causes the error.

bbibbi_yc
Calcite | Level 5
I see. Thank you so much!!!!
Patrick
Opal | Level 21

You need to amend your data step so variable bmi_group gets populated.

data homework_2;
  set homework_1;
  bmi_group=put(bmi,bmi_group.);
run;

For just using a formatted value in a where clause you could also directly write code like:

proc print data=homework_1;
  where put(bmi,BMI_group.)='overweight';
run;

And if you then want to print the bmi values with a format applied you could code like:

proc print data=homework_1;
  where put(bmi,BMI_group.)='overweight';
  format bmi bmi_group.;
run;

 

bbibbi_yc
Calcite | Level 5

I follow your steps and solve the problem!

Thank you so much!!!!!!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 501 views
  • 0 likes
  • 3 in conversation