Numeric field from sql server to SAS

[stat_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,

[PGStats]

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

[stat_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

[RichardinOz]

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

[stat_sas]

Thanks Richard and PG for your valuable input.

Naeem

[jakarman · Accepted solution]

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..