BookmarkSubscribeRSS Feed
Tom
Super User Tom
Super User

Is there any SAS options to control how SAS maps data types from external databases?

 

I know that I could map each variable individually using the DBSASTYPE dataset option,

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n0v4ma1zb9lu99n1728j279rjcqi.htm&docset...

but I am wondering if there is a way to set an option to automatically map all variables with a specific type without having to first identify the specific variables that need to be mapped.

 

In particular I am interested in whether there is a way to control the automatic conversion of very large integer types (longint etc.) into character strings when pulling data from external databases into SAS dataset.  I frequently get access to databases where they are using these extremely large integers as unique identifiers (not for actual numeric data) and think it would make it much easier to work with that data in SAS if those long integer values where automatically converted to digit strings instead of floating point numbers (where they might lose their ability to work as unique identifiers due to truncation/rounding).

 

1 REPLY 1
JBailey
Barite | Level 11

Hi @Tom 

 

*** You may want to jump to the final slide image and check out the environment variable ***

*** I don't believe there is a way to globally map one data type to another ***

 

SAS cannot read the BIGINT data types, directly. There are a couple of ways to handle it.

 

The first is to have your DBA create views that CAST the BIGINT columns to a character data type (Linus' recommendation). You can do this, without creating Teradata views, using explicit pass-thru.

 

I have pulled some slides covering using SAS with Teradata BIGINT from a workshop that I used to deliver. Hopefully, they will help. If the slides require clarification please let me know.

Teradata_BIGINT_01.jpg

 

 

 

Teradata_BIGINT_02.jpg

 

 

Teradata_BIGINT_03.jpg

 

 

Teradata_BIGINT_04.jpg

 

 

Teradata_BIGINT_05.jpg

 

Be careful with TRUNCATE_BIGINT. Doing calculations using BIGINT columns could very well be problematic because you can get incorrect answers.

 

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 314 views
  • 2 likes
  • 2 in conversation