topic Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL in SAS Programming

HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

ANIRBAN2 — Mon, 27 Jun 2022 10:07:42 GMT

I wanted to create table numeric which can store 22 digits and with decimals.

I need to create a table in proc sql only

and run insert query to enter the data.

Eg=123456789987654321.999

this is the numeric value.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

Ksharp — Mon, 27 Jun 2022 10:14:26 GMT

NO. You can't .
But you could try PROC FEDSQL ,there is a BIGINT type variable.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

Kurt_Bremser — Mon, 27 Jun 2022 11:13:50 GMT

You can store the number, but only with precision down to the 15th digit (overall). The imprecision is statistically insignificant.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

ballardw — Mon, 27 Jun 2022 14:59:16 GMT

What exactly does that "number" represent with that many digits where a decimal is important?

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

jklaverstijn — Mon, 27 Jun 2022 15:20:05 GMT

You could try and use explicit SQL pass-through. That way you use the SQL of the target DBMS. But that would only work if the large number is hardcoded in plain text or coming from another dbms table. It could not come from a SAS dataset. SAS' 8-byte numeric is either large magnitude or high precision but not both. Compare that to the 17-byte decimal in SQL Server. Can't fit round pins in square holes.

FedSQL may also be viable here but I have little experience go offer there.

Regards,

- Jan.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

Tom — Mon, 27 Jun 2022 15:38:22 GMT

You cannot store that many digits of precision as a single number in a SAS dataset. SAS has two variable types, fixed length character strings and 64 bit floating point numbers. The maximum number of decimal digits you can store without lose of precision is 15.

Can you just store the value as a string of 22 (or perhaps 23 with the period?) characters?

string='123456789987654321.999';

Is the maximum absolute value just 10E18 like in your example and you only need three digits to the right of the decimal point? Then you could store the first 11 to 15 digits in one number and the rest in another number.

For example split the value into X and Y such that X+Y gets the desired value (if done in an environment that supports such precision).

x=123456789987000000.000;
y=000000000000654321.999;

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

ANIRBAN2 — Mon, 27 Jun 2022 15:47:29 GMT

I have the data in a excel I will write a insert query to upload the data into SAS datasets
So this method how will it work??
Already defined the table its variable name and datatype struggling when using insert query to upload the data. That time I wanted to change the length of the numerical variable so that i could fit the value with precision but I failed.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

ANIRBAN2 — Mon, 27 Jun 2022 15:49:59 GMT

That number is used at some where to calculate the VAR at retail banking. That I am not actually aware totally.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

jklaverstijn — Mon, 27 Jun 2022 16:09:30 GMT

Ah you want it in SAS. Sorry my brain was talking database to me. 😉

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

SASKiwi — Mon, 27 Jun 2022 20:47:28 GMT

Are you referring to retail banking Value at Risk (VaR) ? If so there is no way you need 22 digits of precision for this. At the very least this would be calculated as a whole number (no decimals) and 10 digits can accurately store up to $9,999,999,999. Please post the SAS log of your program so we can advise further.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

ANIRBAN2 — Tue, 28 Jun 2022 03:30:59 GMT

I just hope you can see as template provided by the company(Integrated)

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

ANIRBAN2 — Tue, 28 Jun 2022 03:32:49 GMT

I am in position of inputting sample data. Logs I cannot provide. Please view the snip.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

SASKiwi — Tue, 28 Jun 2022 03:41:57 GMT

That screenshot shows no numbers more than 10 digits long. As others have said, SAS numeric variables can accurately hold 15 digits so I don't see what your problem is. You need to provide a log of your SAS program if it is producing errors and you want help with that.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

SASKiwi — Tue, 28 Jun 2022 03:53:33 GMT

Surely you can run your program with fake data to produce the same errors? There shouldn't be anything confidential about that.

Re: HOW TO STORE A NUMERIC VALUE IN SAS WITH 22 DIGITS INCLUDES DECIMAL

Kurt_Bremser — Tue, 28 Jun 2022 05:03:38 GMT

Excel has the same precision limits as SAS, as it uses the same 8 byte floating point storage. So you can't precisely store a number with 22 digits in both environments.