DATA Step, Macro, Functions and more

Merging tables where primary keys are BIGINT Data types

Accepted Solution Solved
Reply
Contributor jp
Contributor
Posts: 29
Accepted Solution

Merging tables where primary keys are BIGINT Data types

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


Accepted Solutions
Solution
‎01-10-2012 10:10 AM
Trusted Advisor
Posts: 1,301

Merging tables where primary keys are BIGINT Data types

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,301

Merging tables where primary keys are BIGINT Data types

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.

Contributor jp
Contributor
Posts: 29

Re: Merging tables where primary keys are BIGINT Data types

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

5111000018684101632

5111000018684101632

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

5.111E18

5.111E18

5.111E18

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

Respected Advisor
Posts: 4,173

Re: Merging tables where primary keys are BIGINT Data types

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.

Solution
‎01-10-2012 10:10 AM
Trusted Advisor
Posts: 1,301

Merging tables where primary keys are BIGINT Data types

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;

Contributor jp
Contributor
Posts: 29

Merging tables where primary keys are BIGINT Data types

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!

Respected Advisor
Posts: 4,173

Re: Merging tables where primary keys are BIGINT Data types

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.

Trusted Advisor
Posts: 1,301

Merging tables where primary keys are BIGINT Data types

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1329 views
  • 3 likes
  • 3 in conversation