topic proc SQL, convert numeric to character in SAS Procedures

proc SQL, convert numeric to character

deleted_user — Tue, 03 Aug 2010 20:46:45 GMT

Within proc SQL, is it possible to select a numeric column, then check it against character values without an error? I've tried converting it to character but can't seem to get the syntax correct.

If the variable mcc in table x is numeric, the following yeilds an error since mcc is numeric and the where statement contains character values:

proc sql;
create table a as select mcc
from x
where mcc in ('1234','5678');
quit;

Is there a way to convert mcc to character so the where statement works? In case you are wondering, changing the where to values to numeric is not an option.

Thanks

Re: proc SQL, convert numeric to character

Cynthia_sas — Tue, 03 Aug 2010 21:00:11 GMT

Hi:
You should be seeing something like this ERROR message in the log:
[pre]

ERROR: Expression using IN has components that are of different
data types.

[/pre]

But, for purposes of WHERE, you can use the PUT function to convert the compared value to character for the duration of the comparison. See the program log below.

cynthia
[pre]
9331 ods listing;
9332 proc sql;
9333 title 'bad sql where';
9334 select *
9335 from sashelp.class
9336 where age in ('11','12','13')
9337 order by age;
ERROR: Expression using IN has components that are of different
data types.
NOTE: The IN referred to may have been transformed from an OR
to an IN at some point during PROC SQL WHERE clause
optimization.
9338 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

9339
9340

9341 proc sql;
9342 title 'good sql where';
9343 select *
9344 from sashelp.class
9345 where put(age,2.0) in ('11','12','13')
9346 order by age;
9347 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds

[/pre]

Re: proc SQL, convert numeric to character

deleted_user — Tue, 03 Aug 2010 21:51:05 GMT

You are exactly correct and your solution works. Thank you very much.