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

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

 
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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.

View solution in original post

10 REPLIES 10
Afor910327
Obsidian | Level 7

Thanks Kurt, but how do you actually solve this? 

yabwon
Onyx | Level 15

Maybe VVALUE() function?

 

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

All the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Afor910327
Obsidian | Level 7

Thanks you, this one did not work. 

yabwon
Onyx | Level 15
Sorry, my mistake, I forgot about that. Thanks Kurt!

All the best
Bart
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Kurt_Bremser
Super User

@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
Fluorite | Level 6
Field gender not a char.
ballardw
Super User

@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
Fluorite | Level 6

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'

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

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.

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
  • 10 replies
  • 1266 views
  • 3 likes
  • 5 in conversation