BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jp
Fluorite | Level 6 jp
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
FriedEgg
SAS Employee

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

7 REPLIES 7
FriedEgg
SAS Employee

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.

jp
Fluorite | Level 6 jp
Fluorite | Level 6

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

Patrick
Opal | Level 21

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.

FriedEgg
SAS Employee

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;

jp
Fluorite | Level 6 jp
Fluorite | Level 6

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!

Patrick
Opal | Level 21

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.

FriedEgg
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 2707 views
  • 3 likes
  • 3 in conversation