ERROR: Numeric expression requires a numeric format.

[markc]

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

[Tom · Accepted solution]

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.