Numeric field from sql server to SAS

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,230
Accepted Solution

Numeric field from sql server to SAS


Hello Everyone,

I am having problem while processing a field ID in SAS.  This is a numeric field contains 27 digits in Sql server 2008. When I run query in SAS accessing this table through

odbc I am getting correct numbers. But when create a dataset in SAS like

data want;

set schema.have;

run;

I am getting different counts as a result of same query. Any suggestions for this one please?

Regards,


Accepted Solutions
Solution
‎03-19-2014 05:09 PM
Trusted Advisor
Posts: 3,215

Re: Numeric field from sql server to SAS

DBType / DBSASTYpe are the settings for your needed conversion. Part of SAS/ACCESS SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (sql server types) and SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (DBsastype)

If you have proc DS2 (9.4 production, 9.3 latest version experimental) the DBMS types are supported.
You just need to learn that language, some similarties to SCL-language..

---->-- ja karman --<-----

View solution in original post


All Replies
Respected Advisor
Posts: 4,934

Re: Numeric field from sql server to SAS

SAS numbers only have 14 to 15 significant digits. The last 12 digits of your IDs are ignored. Try this test:

data _null_;

x1 = 123456789012345678901;

x2 = 123456789012345678902;

if x1=x2 then put "Same"; else put "Not same";

run;

PG

PG
Trusted Advisor
Posts: 1,230

Re: Numeric field from sql server to SAS

Hi PG,

Thanks for looking into it. I've tried provided syntax and getting "same" as a result. When SAS processes ID within sql server using odbc I don't have any issue because sql server can accomodate data upto 38 digits for numeric fields.  Is there any possibility to process the same within SAS please? Like can I identify this field as a character variable while connecting to sql server?

Naeem

Super Contributor
Posts: 644

Re: Numeric field from sql server to SAS

What you suggest, casting the id as a character variable, is a good solution and I have worked with Oracle databases where a 23 digit id was referenced in SAS as a character variable.  You may have to create a view in SQL server to recast the id variable.  You should do this anyway because even if your summary is done on the server, the result returned to SAS may have non-distinct ids as a result of the 'rounding' during the access.

Richard

Trusted Advisor
Posts: 1,230

Re: Numeric field from sql server to SAS

Posted in reply to RichardinOz

Thanks Richard and PG for your valuable input.

Naeem

Solution
‎03-19-2014 05:09 PM
Trusted Advisor
Posts: 3,215

Re: Numeric field from sql server to SAS

DBType / DBSASTYpe are the settings for your needed conversion. Part of SAS/ACCESS SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (sql server types) and SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (DBsastype)

If you have proc DS2 (9.4 production, 9.3 latest version experimental) the DBMS types are supported.
You just need to learn that language, some similarties to SCL-language..

---->-- ja karman --<-----
🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 262 views
  • 8 likes
  • 4 in conversation