topic Re: Numeric field from sql server to SAS in SAS Programming

Numeric field from sql server to SAS

stat_sas — Tue, 18 Mar 2014 19:56:55 GMT

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,

Re: Numeric field from sql server to SAS

PGStats — Tue, 18 Mar 2014 20:09:21 GMT

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;

Re: Numeric field from sql server to SAS

stat_sas — Tue, 18 Mar 2014 20:33:46 GMT

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

Re: Numeric field from sql server to SAS

RichardinOz — Tue, 18 Mar 2014 23:42:17 GMT

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

Re: Numeric field from sql server to SAS

stat_sas — Wed, 19 Mar 2014 13:05:49 GMT

Thanks Richard and PG for your valuable input.

Naeem

Re: Numeric field from sql server to SAS

jakarman — Wed, 19 Mar 2014 21:09:22 GMT

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