topic Re: proc sql 15-character to number in SAS Programming

proc sql 15-character to number

ilya1 — Wed, 27 Sep 2023 19:01:28 GMT

hi all,

my input replaces last seven digits with zeros. please help!

here's code

proc sql noprint;
create table AAA as
select input(WOOW, best15.) as WOOW_NUM
from TABLE
where BLAHBLAHBLAH
group by 1
order by 1 desc
; quit;

here's original WOOW string:

744811391691773

here's what I am getting back:

744811390000000

many thanks in advance!

Re: proc sql 15-character to number

PaigeMiller — Wed, 27 Sep 2023 19:09:51 GMT

Some 15 digit numbers (and longer numbers) must be stored as character strings. SAS cannot represent some 15 digits (or longer) numbers exactly, as you have seen.

This has nothing to do with PROC SQL

Re: proc sql 15-character to number

ilya1 — Wed, 27 Sep 2023 19:13:25 GMT

hmm.. that's not useful. i need the numbers to load into a subsequent query.

Re: proc sql 15-character to number

SASKiwi — Wed, 27 Sep 2023 19:17:50 GMT

What is WOOW? If it's not a measure then leave it as character as 15 digits is at the maximum of what a SAS numeric variable can hold accurately.

If you must convert, try adding a format to ensure 15 digits are displayed.

proc sql noprint;
create table AAA as
select input(WOOW,  best15.) as WOOW_NUM format = 15.
from TABLE
where BLAHBLAHBLAH
group by 1
order by 1 desc
; quit;

Re: proc sql 15-character to number

ilya1 — Wed, 27 Sep 2023 19:21:59 GMT

need WOOW to run subquery against in a different database where it is a number. so formats mismatch is an issue.

Re: proc sql 15-character to number

SASKiwi — Wed, 27 Sep 2023 19:22:54 GMT

A DATA step for your example works:

data _null_;;
  var = '744811391691773';
  var_num = input(var, best15.);
  format var_num best15.;
  put _all_;
run;

Re: proc sql 15-character to number

SASKiwi — Wed, 27 Sep 2023 19:25:16 GMT

You can CAST or INPUT a character variable to a number in SQL to get the column types to align in another database.

Re: proc sql 15-character to number

PaigeMiller — Wed, 27 Sep 2023 19:25:55 GMT

In SQL you can match to the 15 digit number converted to character

Something like

...
on a.fifteendigitcharvar=put(fifteendigitnumber,15.)

Re: proc sql 15-character to number

Patrick — Thu, 28 Sep 2023 06:56:57 GMT

The biggest integer SAS can store with full precision got 16 digits: 9007199254740992

The string in your example got only 15 digits and though SAS can store it with full precision in a numerical variable (see example below).

Not sure where you are losing precision but it's highly likely not happening within SAS if what you show us is what you've got.

data have;
  length WOOW $16;
  WOOW='9007199254740992'; output;
  WOOW='744811391691773'; output;
run;

proc sql;
  select input(WOOW,best16.) as WOOW_NUM format=best32.
  from have
  ;
quit;

Re: proc sql 15-character to number

Tom — Thu, 28 Sep 2023 15:21:15 GMT

There is a limit on the precision that SAS/Access can transfer that is a little lower than the actual precision that SAS can store. So even if your 15 digit numbers might be smaller than the maximum contiguous integer that SAS can store.

500  data _null_;
501    max=constant('exactint');
502    put max=comma24.;
503  run;

max=9,007,199,254,740,992

you might still be losing precision by trying to transfer the value as a number.

Convert the value to a string and transfer it as a string.

How to convert the value to a string not the same on every database, so look up the syntax for your system. Usually they have implemented some version of CAST() function to do that.

So you might get the list of values from one database (or table).

create table list as
  select * from connection to mydb
( select cast(large_decimal_var as char(20)) as ids
  from myschema.mytable1
) ;

Then upload the list of values into another database

create table mylib2.list as select * from list;

Then use that list of subset data from a table there.

create table final as 
select * from connection to mydb2 
(select a.* from aschema.atable a
  where large_decimal_value in (select cast(id as decimal(20)) from tempschema.list)
);

Re: proc sql 15-character to number

Patrick — Fri, 29 Sep 2023 00:58:40 GMT

@Tom wrote:

There is a limit on the precision that SAS/Access can transfer that is a little lower than the actual precision that SAS can store. So even if your 15 digit numbers might be smaller than the maximum contiguous integer that SAS can store.

@Tom Can you please share where that's documented and how to figure out what the biggest integer for full precision in such a scenario is. I'm only aware of docu entry Numerical Accuracy in SAS Software which doesn't mention such SAS/Access specifics.

Re: proc sql 15-character to number

Kurt_Bremser — Fri, 29 Sep 2023 05:23:02 GMT

Two things happening here: the number is larger than the source by a factor of 10, and it is rounded by 10000000. With the code you show, this CAN NOT HAPPEN, period. Please show the complete log from your real code.