topic Merging tables where primary keys are BIGINT Data types in SAS Programming

Merging tables where primary keys are BIGINT Data types

jp — Mon, 09 Jan 2012 04:14:01 GMT

Hello,

I'm using SAS EG 4.3, and I'm trying to find a way of coercing BIGINT variables to either read correctly or be read as text, when being read from the SQL Server.

The only solution I've been able to find so far is to use a pass-through query and cast the variable into an appropriate format. However since the variables of interest are primary keys in multiple tables this becomes quite annoying always having to execute a pass through query first.

If anyone has any ideas, or could point me in the right direction I would be most grateful.

Cheers

Jason

Merging tables where primary keys are BIGINT Data types

FriedEgg — Mon, 09 Jan 2012 15:29:48 GMT

If you are using SAS/ACCESS for Microsoft SQL Server I am not sure why you would have an issue with BIGINT data type. In SQL Server SQL_BIGINT represents a integer value from -2^63 to 2^63. SAS will read these columns as format '20.' which should fit the smallest and largest possible number without issue. When moving data from SAS to SQL Server I believe the fomat used is SQL_DOUBLE or SQL_NUMERIC, the difference I suppose may cause confusion in the database. You can try using the option dbtype in SAS to cast the variable to SQL_BIGINT type. If you would give more information about the specifics of your issue maybe someone can be of more help. If you are using SAS/ACCESS Interface to ODBC the issue may be different.

Re: Merging tables where primary keys are BIGINT Data types

jp — Tue, 10 Jan 2012 01:28:44 GMT

I'm using SAS to connect to a SQL Server, using what I belive is "SQLOLEDB".

When I use SAS to read tables from the datawarehouse I end up with keys that become duplicated when in fact they should be unique. This as far as I can figure is because SAS is having trouble reading the BIGINT Datatype.

Running the query in SAS I obtain the following keys back as a result:

5111000018684101632

+ 16 other identical rows....

If I don't coerce the keys to format 20. (using format=20. in the SQL procedure) the keys above display in the following way:

5.111E18

+ 16 other identical rows....

When I perform a pass-through query in SAS casting the key as char(20) I get the following output:

5111000018684101752

5111000018684101760

5111000018684101762

5111000018684101759

5111000018684101763

+ 16 other different rows....

When I run the query in SQL Server Management Studio:

5111000018684101752

5111000018684101760

5111000018684101762

5111000018684101759

5111000018684101763

+ 16 other different rows....

Hope this somewhat clarifies my original post.

Regards

Jason

Re: Merging tables where primary keys are BIGINT Data types

Patrick — Tue, 10 Jan 2012 02:53:47 GMT

Dependent on the OS the SAS Server is running on a SQL BIGINT column can hold a larger number with full precision than a SAS numeric variable can.

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695157.htm

Even when not using pass-through SQL (but SAS SQL) the SAS/Access engine will try to send as much of the SQL query to the DBMS as possible. So joining 2 DB tables using SQL (even SAS SQL) will return the correct number of rows as long as processing happens on side of the DB. Look up option "sastrace" which allows you to get logging info of what has been sent to the DB for execution.

When you get the result back from the DB and the result is stored in a SAS table then you will loose full precision for this "bigint keys". That's the reason for these duplicate keys.

The only way I can think of to keep the original key value is conversion to a character. Not sure if you could simply do this as a calculated field (eg. by using the EG wizard and a put expression) - but you could give it a try and by using "sastrace" check where the numeric to character conversion happens.

Merging tables where primary keys are BIGINT Data types

FriedEgg — Tue, 10 Jan 2012 15:10:44 GMT

Forgot about the possible issues with percision. Thanks OP for the expanded explaination of your issue. You could consider using stored SAS/ACCESS views to keep your pass-through query and allow you to more ealisy access the table elsewhere. Here is an example;

libname myviews '/temp/sas/view';

proc sql;

create view myviews.view1 as

select cast(id as char(20)),foo,bar

from mssql.table1

using libname mssql sqlsvr

nopromt="uid=someone; pwd=somepass; dsn=sqlservr;";

quit;

You may still want to consider looking at the dbsastype option as a solution, I do not have experience using it for this type of issue however it may be what you are looking for.

data foo;

set sqlservr.table1(dbsastype=(id=char(20)));

run;

Merging tables where primary keys are BIGINT Data types

jp — Wed, 11 Jan 2012 03:40:45 GMT

Cheers for the help on this.

My solution based off the above thinking was to use multiple pass through queries to create a library of views and then call these views rather than the original datawarehouse.

While I have a bit of work porting the DW tables into views, this actually helps me solve another problem that has been troubling me.

Merci!

Re: Merging tables where primary keys are BIGINT Data types

Patrick — Wed, 11 Jan 2012 04:46:54 GMT

The reason why I didn't propose to create SAS views are:

- Joining 2 SAS views results in all data pulled from the SQL Server into SAS for joining

- Existing indexes which might exist on the original SQL tables will not be used for the join using SAS SQL views

If you really want to create these SAS views then I would suggest that you use dictionary.columns to dynamically create the code for these views.

Merging tables where primary keys are BIGINT Data types

FriedEgg — Wed, 11 Jan 2012 15:03:16 GMT

Patrick once again points out some very key points to consider. These views are optimally used for ETL other than joins.