Is there any way to read in a bigint field using SAS EG Query Builder?

[saslover15]

Note, we do have the environmental option set in an autoexec process flow that runs before our main process flow to allow bigint:

SET=TRUNCATE_BIGINT 'YES'.

However, this still does not allow Query Builder to successfully read in a bigint field.

In hand-coding in PROC SQL, we either use the DIGITS SQL function, or we cast it to a CHAR20 on the read in, which works fine, but is there any way to do something similar in Query Builder?

I have tried using the PUT function to convert it to character, but that has no impact - it still reads in incorrectly.  And Query Builder doesn't know what the DIGITS function is.  I am unable to find any function in Query Builder that can help with this.

Does anyone know how to get Query Builder to successfully read in a bigint field?

[TomKari]

Yes, that's a toughie! I assume your field is an eight byte integer in the ib4. type of format?

What happens if you pull it into a query with no attempt to transform it? What does PROC SQL convert it to, and does the internal of the field make any sense?

Tom

[saslover15]

I can't answer your first question.  

As for the second question, with no attempt to transform it, it reads in wrong.  It displays in a SAS dataset with the last 3 to 4 digits incorrect.  The first 15 so far are displaying correctly, but positions 16-19 tend to display incorrectly.  If I click into the sas data set that is displayed in SAS EG, it then appears in scientific notation.

[Tom]

---

@saslover15 wrote:

I can't answer your first question.  

As for the second question, with no attempt to transform it, it reads in wrong.  It displays in a SAS dataset with the last 3 to 4 digits incorrect.  The first 15 so far are displaying correctly, but positions 16-19 tend to display incorrectly.  If I click into the sas data set that is displayed in SAS EG, it then appears in scientific notation.

---

So it is converting the value to a number in SAS.  All numbers in SAS use 8 byte floating point representation. Your values have exceeded the number of digits that be exactly represented in that format.

You should convert the values to strings before copying them into SAS datasets.

Do these BIGINT values represent actual numbers?  Or are they just identifiers?    Do you every need to find the MEAN of some of these numbers?

[saslover15]

That's the problem - I can't figure out a way to read it in as a char so that the numbers will read in correctly.  EG automatically creates the dataset, so I am not copying it to a dataset, rather the dataset is the necessary output from the Query Builder function in SAS EG.  

I have tried adding a "Computer Column" in the Query Builder "Select Data" tab and using the PUT function to convert it to CHAR 20 as it reads in, but it does not work and the number reads in as if I had done nothing at all.

This number is just an identifier....not for calculations.  But it NEEDS to read in correctly - and as a CHAR20 would be great - , otherwise every process downstream will be incorrect.

[Tom]

You cannot use the PUT() function (unless your libname is using a SAS/Access engine that SAS knows how to push the PUT() function into the database) since that will run AFTER the number as already been pulled into SAS.

You need to use DBSASTYPE dataset option.

http://support.sas.com/kb/39/831.html

You could setup a view that uses that dataset option.

libname mydb ...... ;
data myview;
  set mydb.mytable (dbsastype=(myid='char(20)'));
run;

Then use the query builder on that view?

Or better yet have the remote DBA setup SAS friendly views that you can query instead of the current tables/views.  That would also give you a place to convert long variable or table names to be SAS compatible names.

[saslover15]

Right - I looked into that, but I would like a solution that does NOT involve SAS hand-coding and that only uses the Query Builder.  I have people who are not SAS coders who need to use the Query Builder, as-is.  If Query Builder simply cannot handle bigint, that is fine, but I need to know that so that we know that anything dealing with bigint must be handled with hand-coding.

[saslover15]

Oh, and good to know on the PUT function.  I did not know that it runs after-the-fact, so that explains why that isn't working.

[TomKari]

Okay, this is clear now. When SAS is converting your bigint to a SAS floating point number, it sounds like it's keeping the first 15 significant digits, which is all that can be stored in a SAS floating point number. If you were using it for calculations, 15 significant digits might be fine, but since it's an identifier it has to be exact.

There's a lot of discussion about whether numeric identifiers should be stored in numeric fields or character fields, and this is one of the reasons. A couple of options:

Create two new fields in your database, one with the first 10 digits of the ID, the other with the last 10 digits.

Create a view on the table, and add a field which is a CAST of your ID into a char(20) variable.

I'll post any more that I can think of.

Tom

[Patrick]

---

@saslover15 wrote:

Oh, and good to know on the PUT function.  I did not know that it runs after-the-fact, so that explains why that isn't working.

---

I've never used it and right now don't have an environment to test BUT....  The SAS EG query builder provides the option to convert your code into explicit SQL (=SQL in the DB flavor and directly sent to the database for execution).

So... If this means that functions used for computed columns get used in the pass-through SQL block then you could define a cast() function there.

If above works then you could:

1. Have a query builder node for everything to be done on the database side (so only processing database tables)

    - i.e. cast columns to a datatype suitable to load into SAS tables (assumed: you must use DB functions)

   -  reduce data volumes directly on the database side

2. Use a downstream EG node for anything to be done on the SAS side (if still required).

As an option for step 1:

Define the result of the Query as a View and not a Table.

[mkeintz]

I don't suppose there is a way to get EG Query Builder to use PROC DS2.  But if there were, you could represent BIGINT faithfully.

[Patrick]

---

@mkeintz wrote:

I don't suppose there is a way to get EG Query Builder to use PROC DS2.  But if there were, you could represent BIGINT faithfully.

---

For calculations yes but in the moment when you write the data to a target SAS table you've got again 8 bytes only to store the numeric value so issue remains.

[saslover15 · Accepted solution]

Thank you everyone for your contributions.  Since we now know that Query Builder cannot correctly read in a bigint field due to the fact that SAS numeric fields only go up to 8 bytes or 15 digits, the work-around solution that works for us is to put in a piece of hand-coded PROC SQL that casts the bigint field to a char 20.  And as Tom suggested, we will use Query builder off of the output from that piece of hand-coding, or we may just continue to hand-code the rest, depending on the situation.  Note, we tried unsuccessfully to use DBSASTYPE in a datastep.  It generated errors we were not able to resolve, possibly due to our own infrastructure, but casting to char 20 in a PROC SQL works great and reliably every time.  Hopefully it is on SAS's radar to try to enhance Query Builder so that someday it will be able to read in bigint fields.