We are moving off Netezza to Snowflake and have discovered that SAS handles BIGINT formats <= 15 and truncates anything >15. Is there a way to capture BIGINT values >15 without casting to character?
According to the documentation - no:
Note: When performing calculations on numeric values and when storing numeric values, SAS maintains up to 15 digits of precision. When you read values that contain more than 15 decimal digits of precision from a database into SAS, the values that SAS reads are rounded to meet this condition. When you use a large numeric value in a WHERE clause, this rounding can cause unexpected results, such as not selecting desired rows. For noncomputational purposes, such as storing ID values or credit card numbers, you can read the data in as character data.
When you read numbers in from an external DBMS that supports precision beyond 15 digits, you can lose that precision. You cannot do anything about this for existing databases. However, when you design new databases, you can set constraints to limit precision to about 15 digits. Alternatively, you can select a numeric DBMS data type to match the numeric SAS data type.
"Reading the data in as character is not a desirable method because the variables are keys." - In my experience, most data keys are character, be it customer IDs, account IDs and so on. Their big advantage is they don't have precision problems like numeric columns. You could create a character copy of the Bigint Snowflake key in Snowflake and use that when reading into SAS.
Keys should always be stored as character. Your DB designers need to correct this ASAP.
And consider to switch to the use of UUIDs, which are stored as 16-byte strings internally, but represented as 36-character strings when exported or displayed.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.