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


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,

1 ACCEPTED SOLUTION

Accepted Solutions
jakarman
Barite | Level 11

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

5 REPLIES 5
PGStats
Opal | Level 21

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
stat_sas
Ammonite | Level 13

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
Quartz | Level 8

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
Ammonite | Level 13

Thanks Richard and PG for your valuable input.

Naeem

jakarman
Barite | Level 11

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

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
  • 5 replies
  • 839 views
  • 8 likes
  • 4 in conversation