- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
I am trying to read a numeric variable from a table using PROC SQL but I want the variable which it falls into to be a character variable containing the various formats depending on its value.
proc format;
value price_bands
. = "$0-$5,000"
0-5000 = "$0-$5,000"
5001-10000 = "$5,001-$10,000"
run;
proc sql;
create table demo_test as
select a.*,
b.price as price_band format=$price_bands.
from table1 as a
left join table2 as b
on a.key= b.key
quit;
Problem is, I am getting this error:
ERROR: Numeric expression requires a numeric format.
Any help will be greatly appreciated and Liked.
Kind regards,
Mark
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Remove the extra $ in the format name in the FORMAT= option of your SQL code.
You defined a numeric format, but in your SQL code you asked for a character format with a similar name. PROC SQL is complaining because the variable you are trying to attach the format to is numeric, not character. If you attempt to attach a format of the wrong type in a FORMAT statement of a DATA step SAS will auto-correct to using a similarly named format of the appropriate type for the variable. But in a PROC SQL SELECT statement it causes a hard error.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Remove the extra $ in the format name in the FORMAT= option of your SQL code.
You defined a numeric format, but in your SQL code you asked for a character format with a similar name. PROC SQL is complaining because the variable you are trying to attach the format to is numeric, not character. If you attempt to attach a format of the wrong type in a FORMAT statement of a DATA step SAS will auto-correct to using a similarly named format of the appropriate type for the variable. But in a PROC SQL SELECT statement it causes a hard error.