Format large number?

[amorts]

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?

[jklaverstijn]

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

Thanks in advance, Jan.

[Patrick]

@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]

Ok thanks. I don't have access to the code right now but I'll post an example on Monday

[amorts]

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 · Accepted solution]

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]

Ok thanks.
Why doesn't it inherit the data type from the incoming data? What does it default to?

[Tom]

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]

thank you very much, that worked!

[jklaverstijn]

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.

[Kurt_Bremser]

@Siarhei_B is not the OP, and cannot accept a solution for the thread.

[jklaverstijn]

Ah yes I see. Thanks for pointing that out Kurt. I will make a correction.

[Kurt_Bremser]

---

@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.