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
@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.
gender is numeric and has a format attached to it that displays strings.
Thanks Kurt, but how do you actually solve this?
Maybe VVALUE() function?
proc sql;
create table costmetic_promotion_v2 as
select *
from Cosmetic_promotion
where VVALUE(gender) = "Female";
quit;
All the best
Bart
Thanks you, this one did not work.
The VVALUE function is only available in data step code (not in where conditions):
data want;
set have;
if vvalue(gender) = 'Female';
run;
@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 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.
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'
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: