Tell SAS to read a database field as CHAR instead of INT or BIGINT

Yesterday a frustrated SAS user complained on Twitter. He’s working with a database that stores an ID field as a big long number (perhaps using the database BIGINT type), and SAS can’t display a number greater than 15 digits. Well, it’s actually 16 digits, depending on the value:

%put Biggest Exact Int = %sysfunc(constant(EXACTINT,8));
>> Biggest Exact Int = 9007199254740992

It’s a controversial design decision to use an integer to represent an ID value in a database. You might save a few bytes of storage, but it limits your ability to write programs (not just SAS programs) that have to store and manipulate that value. And if you don’t need to do math operations with the ID, your data consumers would rather see a nice character value there.

Fortunately, when working with databases, you can tell SAS to read numeric values as character values into your SAS data sets. In addition to solving the precision problem I’ve just described, this can also help when you need to join database fields with other source systems that store their key fields differently. It’s usually much easier to convert the field “on the way in” rather than try to mangle it after you’ve already read in the records. Use the DBSASTYPE= data set option to tell SAS how to read database fields. Here’s a sample SAS program that shows how I access a table using ODBC, one step without and one step with the DBSASTYPE= option.

libname wpblogs odbc datasrc="wpblogs";
 
options obs=10;
data users_IDint (keep=ID display_name);
  set wpblogs.wp_users;
run;
 
data users_IDchar (keep=ID display_name);
  set wpblogs.wp_users 
    (dbsastype=(ID='char(20)'));
run;

Here are the resulting tables; you can see the simple difference. One has ID as a number, and one has it as a character. Magic!

The DBSASTYPE= option is supported for virtually all SAS/ACCESS database engines, including the ubiquitous SAS/ACCESS to ODBC.

Oh, and you might be wondering how things turned out for our frustrated user on Twitter. Our SAS Cares social media team heard his plea and responded — as they always do. And our user not only found the information useful, he took it a step further by replying back with an additional syntax tip.

@cjdinger @SAS_Cares @SASsoftware Multiple vars can be reformatted in one PROC SQL statement for a join. #SQLprobs pic.twitter.com/2kEWdmOmPv

— Sam Van Horne (@LearningPlaces) May 19, 2016

The post Tell SAS to read a database field as CHAR instead of INT or BIGINT appeared first on The SAS Dummy.