Geek Answers Handbook

SAS Read Bit Data Type in SQL Server 2005

I have a sql server 2005 database that has a table with a data type bit column. When I look at the data in the sql server management studio, I see the column value as 0 or 1, when I pull out SAS, I see 0 or -1, it looks like SAS, negates the value 1. Does anyone have an explanation? Thanks.

+7
source share
1 answer

I believe that you should use libname oledb to connect to SQL Server with SAS. I can replicate your problem here: -

SQL Server Code for Creating Dummy Data

 create table dbo.tbl ( tblId int identity(1,1) not null constraint pk_tbl_tblId primary key, bool bit not null, ) go insert into dbo.tbl(bool) values(0) insert into dbo.tbl(bool) values(1)

SAS code using OLEDB

 libname imm oledb provider=sqloledb properties=( "Integrated Security"=SSPI "Persist Security Info"=False "Initial Catalog"=test "Data Source"=localhost ); proc print data=imm.tbl; run;

Fingerprint: -

 Obs tblId bool 1 1 0 2 2 -1

SAS code using PROC SQL

It seems that using PROC SQL should solve your problem.

 proc sql noprint; connect to sqlservr ( server='localhost' database='test' 'Integrated Security'='SSPI' 'Persist Security Info'='False' ); create table test as select * from connection to sqlservr ( select * from dbo.tbl ); disconnect from sqlservr; quit; proc print data=test; run;

Fingerprint: -

 Obs tblId bool 1 1 0 2 2 1
+5
source

More articles:


All Articles