BookmarkSubscribeRSS Feed
xiy513
Calcite | Level 5

I defined a format using proc format, and then try to use it in proc sql, but an error message keeps coming out and I don't know what I did wrong. Hope someone can help me with it, thanks a lot!

 

17   proc format library=Project;
18   value $ range
19   810-high = '810+'
20   790-809 = '790-809'
21   770-789 = '770-789'
22   750-769 = '750-769'
23   730-749 = '730-749'
24   710-729 = '710-729'
25   690-709 = '690-709'
26   670-689 = '670-689'
27   650-669 = '650-669'
28   630-649 = '630-649'
29   low-629 = '<630'
30   other = 'Null or Zero';
31   run;
32   OPTIONS FMTSEARCH=(Project);
33   run;
34
35   proc sql;
36   create table project.n1 as
37   select score,  put(Score,$range.) as range
38   from
39   project.student;

40  quit;

The message is like:
error: character format $range in PUT function requires a character argument.

6 REPLIES 6
Reeza
Super User

This isn't a character format, its a numeric format - applied to numbers.

 

Remove the dollar sign from PROC FORMAT VALUE statement and the PUT function.

xiy513
Calcite | Level 5

Thanks for replying, I did what you said but it returned another message like ERROR: The format $RANGE was not found or could not be loaded. I did remove the dollar sign from value statement. 

Reeza
Super User

Post your code and log. 

xiy513
Calcite | Level 5

It worked after all, thanks. But the problem is the format 'range' has to be a character format according to the requirement, so I wonder if there is a way to do it? 

Shmuel
Garnet | Level 18

In your sql code you used the format in "put(Score,$range.) as range"

 

What type is the score variable ? If sas asks for $format it means that the variable is a alphanumeric,

then use:  put(input(score, best 5.),range.) as range and remove the $ from the value statment .

Kurt_Bremser
Super User

@xiy513 wrote:

It worked after all, thanks. But the problem is the format 'range' has to be a character format according to the requirement, so I wonder if there is a way to do it? 


The result of the put() function is always of type character.

 

To really illustrate your issue, you need to supply example data; do so in a data step.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 492 views
  • 0 likes
  • 4 in conversation