BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
amorts
Calcite | Level 5

I am running some Proc SQL queries e.g. MAX(variable) where the result cannot be displayed correctly due to the size of the number. How do I format this within the query to see the actual number?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You can specify the format to attach to a variable you are deriving in SQL code by adding the FORMAT= keyword.

data have ;
  num=5E13 ;
  format num 17.2 ;
run;
proc sql ;
  create table want as
    select max(num) as unformatted 
         , max(num) as formatted format=17.2 
    from have
  ;
quit;

proc print data=want;
run;
Obs    unformatted            formatted

 1          5E13      50000000000000.00

 

Update from Community Manager @BeverlyBrown: Further context to the solution from @Tom in this comment.

View solution in original post

12 REPLIES 12
jklaverstijn
Rhodochrosite | Level 12

Can you add an example of your currently attemped code and data so we get the picture (literally)?

 

Thanks in advance, Jan.

Patrick
Opal | Level 21

@amorts

The correct answer might depend on the details of what you're doing which you haven't told us. As a generic answer: Try using format BEST32., i.e. in your SQL:

 

select

  max(<variable>) as max_var format=best32. ,

  ....

amorts
Calcite | Level 5
Ok thanks. I don't have access to the code right now but I'll post an example on Monday
amorts
Calcite | Level 5
Ok so the field is stored as a num format 17.2.

The result from the max is 5E8

Applying the best32. Works and returns the result 500000000
Tom
Super User Tom
Super User

You can specify the format to attach to a variable you are deriving in SQL code by adding the FORMAT= keyword.

data have ;
  num=5E13 ;
  format num 17.2 ;
run;
proc sql ;
  create table want as
    select max(num) as unformatted 
         , max(num) as formatted format=17.2 
    from have
  ;
quit;

proc print data=want;
run;
Obs    unformatted            formatted

 1          5E13      50000000000000.00

 

Update from Community Manager @BeverlyBrown: Further context to the solution from @Tom in this comment.

amorts
Calcite | Level 5
Ok thanks.
Why doesn't it inherit the data type from the incoming data? What does it default to?
Tom
Super User Tom
Super User

PROC SQL will not assign formats to derived variables. It really cannot tell the difference between a function like MAX() that does not change the type or range of values and an operation like DATEPART() that could change the meaning of the numbers or SUM() that could change the range of values and hence require a different width for format.

 

SAS will normally using BEST12. format to display numbers that do not have a format specification.

Siarhei_B
Calcite | Level 5
thank you very much, that worked!
jklaverstijn
Rhodochrosite | Level 12

Hi @Siarhei_B 

 

Could you flag the correct answer as such? This would tell us which of the many responses is the correct one and would also add to the karma of the person that gave that answer.

 

Correction: that would be a question for @amorts.

 

Regards,

-- Jan.

jklaverstijn
Rhodochrosite | Level 12
Ah yes I see. Thanks for pointing that out Kurt. I will make a correction.
Kurt_Bremser
Super User

@jklaverstijn wrote:
Ah yes I see. Thanks for pointing that out Kurt. I will make a correction.

Won't do much. The threadstarter has not visited the communities since 2017.

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
  • 12 replies
  • 19674 views
  • 1 like
  • 6 in conversation