topic Format large number? in SAS Programming

Format large number?

amorts — Sat, 07 Oct 2017 09:03:22 GMT

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?

Re: Format large number?

jklaverstijn — Sat, 07 Oct 2017 09:16:43 GMT

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

Thanks in advance, Jan.

Re: Format large number?

Patrick — Sat, 07 Oct 2017 10:52:13 GMT

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

....

Re: Format large number?

amorts — Sat, 07 Oct 2017 11:26:50 GMT

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

Re: Format large number?

amorts — Mon, 09 Oct 2017 08:49:42 GMT

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

Re: Format large number?

Tom — Thu, 11 Aug 2022 13:44:34 GMT

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.

Re: Format large number?

amorts — Mon, 09 Oct 2017 15:19:35 GMT

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

Re: Format large number?

Tom — Mon, 09 Oct 2017 15:44:34 GMT

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.

Re: Format large number?

Siarhei_B — Sun, 15 Nov 2020 22:36:34 GMT

thank you very much, that worked!

Re: Format large number?

jklaverstijn — Thu, 26 Nov 2020 10:34:24 GMT

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.

Re: Format large number?

Kurt_Bremser — Thu, 26 Nov 2020 09:45:08 GMT

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

Re: Format large number?

jklaverstijn — Thu, 26 Nov 2020 10:33:32 GMT

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

Re: Format large number?

Kurt_Bremser — Thu, 26 Nov 2020 11:06:49 GMT

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