SAS Enterprise Guide

Desktop productivity for business analysts and programmers
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
markc
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

 

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

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.

 

 

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 7632 views
  • 1 like
  • 2 in conversation