7

I have a sql server 2005 database that has a table with a column of data type bit. When I look at the data in sql server management studio I see the column value as 0 or 1, when i pull with SAS I see 0 or -1, is like SAS is negating the 1 value. Anyone have an explanation for this? Thanks.

Haeflinger
  • 465
  • 1
  • 5
  • 15
  • this is still an issue in SAS 9.3, so we are designing our tables to avoid the BIT data type. No idea why, feels like there should be a SAS note on this.. – Allan Bowe Jun 28 '16 at 13:20

1 Answers1

5

I reckon you must be using libname oledb to connect to SQL Server from SAS. I'm able to replicate your problem here:-

SQL Server code to generate 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;

The print out is:-

Obs          tblId    bool

1              1      0
2              2     -1

SAS code using PROC SQL

It seems like using PROC SQL should fix 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;

The print out is:-

Obs          tblId    bool

1              1      0
2              2      1
limc
  • 39,366
  • 20
  • 100
  • 145
  • 2
    This will work for me, but that will require anyone who connects to the database with SAS to be required to use proc sql; I think I am going to have to convert to tinyint to make sure this the data will be consistent regardless of how is access with SAS. – Haeflinger Mar 17 '11 at 15:35