Error in Proc SQL Where Clause

[Afor910327]

Hi all,

I cannot understand, why this does not work, any suggestions?

 proc sql;
2416  create table costmetic_promotion_v2 as
2417  select *
2418   from Cosmetic_promotion
2419   where gender = "Female";
ERROR: Expression using equals (=) has components that are of different data types.
2420  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[Kurt_Bremser]

gender is numeric and has a format attached to it that displays strings.

[Afor910327]

Thanks Kurt, but how do you actually solve this? 

[yabwon]

Maybe VVALUE() function?

proc sql;
create table costmetic_promotion_v2 as
select *
from Cosmetic_promotion
where VVALUE(gender) = "Female";
quit;

All the best

Bart

[Afor910327]

Thanks you, this one did not work. 

[Kurt_Bremser]

The VVALUE function is only available in data step code (not in where conditions):

data want;
set have;
if vvalue(gender) = 'Female';
run;

[yabwon]

Sorry, my mistake, I forgot about that. Thanks Kurt!

All the best
Bart

[Kurt_Bremser · Accepted solution]

---

@Afor910327 wrote:

Thanks Kurt, but how do you actually solve this? 

---

Application of Maxim 3: Know Your Data.

You can create a quick sample of your dataset where you remove the format:

data test;
set Cosmetic_promotion (obs=100);
format gender;
run;

so you can see the raw values.

Or you run a proc contents on the dataset to see the name of the format. Then you go looking in SASHELP.VCATALG for the entry with OBJNAME=name of your format. Then you run proc format on the catalog you find in that entry, and select the format. This will display the contents of the format, and allow you to do a translation.

[Alexandr]

Field gender not a char.

[ballardw]

---

@Alexandr wrote:
Field gender not a char.

---

Actually it could be character but still have a format that displays a value such as Female. If you do a comparison you have to use either the Non-formatted value, or if you know the format name apply the format to the value in the comparison.

Proc format library = work;
value $fm
'F' = 'Female'
'M' = 'Male'
;
run;

data example;
	input gender $;
	format gender $fm.;
datalines;
F
M
M
F
;

proc sql;
   create table want as
	select gender 
	from example 
	/* use actual value*/
	where gender='F'
	;
   create table want2 as
	select gender 
	from example 
	/* use formatted value*/
	where put(gender,$fm.)='Female'
	;
quit;

The use of the formatted value in this case is not needed. But I have formats that are used to create groups from 100's of code values into 20 or so groups. I could use an IN operator to check a list of values but the code gets ugly.  So I can use something like :

if put(sitecode,$sitefmt. ) = "Region X" then ....

This approach is especially helpful as Sites get added to the list moderately frequently. Then I only need to add the Site code value to the format and the code for the comparison works as intended. Otherwise I would have to search through all of the code to change a bunch of IN comparisons.

And there are multiple formats associated with the site codes to reflect different uses such as different geographical regions, activities and other "stuff". The formats avoid having to create bunch of extra variables as well.

[Alexandr]

Condition  where gender='F' not return Error described in topic.

So, format may be:

Proc format library = work;
    value fm
        1 = 'Female'
        2 = 'Male'
    ;
run;

 and WHERE should be:

where put(gender, fm.) = 'Female'