Transcoding help needed (I think)

[PaigeMiller]

We access a database via SAS, we work with a variable that is numeric and has format date9. and is listed as Transcode='No'. A contractor who works in some other part of the world (I really don't know where) access the same database via SAS and this exact same variable is listed as Text and Transcode='Yes'. How can this happen, and more importantly, how do we fix this?

[Ksharp]

In genereal, numeric type variable no need to "Transcode" ,so you get Transcode='No'.
But for your contractor,maybe another story, maybe he/she use some options(or use different database driver/client) to convert this numeric type variable into character type . Like:

data have;
set database.have(dbsastype=( num_var='char(20)' ));
run;


And you can let contractor convert it to be numeric type by:
data have;
set database.have(dbsastype=( num_var='num' ));
run;

Anothe option is DBTYPE for SAS to DB. Check DOC of DBSASTYPE and DBTYPE at support.sas.com

[Tom]

What database?

There are a number of databases now (thinking of Redshift and Snowflake) that allow you to define something that acts like a database but is really a collection of text files.  When you access the "tables" the database makes up the metadata on the fly.  So language options like which order to display month number and day number in date strings might cause the same text file to be read as valid dates by one and invalid dates (hence text strings) by another.

[PaigeMiller]

The data base is only listed as SQL Server.

[whymath]

Maybe contractor can not read date with format date9., try an international format, like e8601da.

[Patrick]

@PaigeMiller The usual question is like always: "What's different between the two runs?"
1. Is this running identical code?

2. Are you using the same or different SAS Servers?

3. What's the database?

4. Is it a distributed database with potentially region specific views?

I feel @Tom is onto something here. 

You could via explicit pass-through verify if the data type is the same on the DB side as well as get the db local. 

Then on the SAS side I'd compare again settings like the local. If there are no differences then you would likely need to look into the drivers and the like. 

[PaigeMiller]

---

@Patrick wrote:

@PaigeMiller The usual question is like always: "What's different between the two runs?"
1. Is this running identical code?

2. Are you using the same or different SAS Servers?

3. What's the database?

4. Is it a distributed database with potentially region specific views?

I feel @Tom is onto something here. 

You could via explicit pass-through verify if the data type is the same on the DB side as well as get the db local. 

Then on the SAS side I'd compare again settings like the local. If there are no differences then you would likely need to look into the drivers and the like. 

---

1. Yes

2. No SAS server, this is base SAS 9.4

3. SQL Server

4. I doubt it, but can't say for sure.

Will try passthru

[ballardw]

My first though was "show me the code" and how they connect.

I was wondering if one is using ODBC (the one getting character values) and another one of the SAS/ACCESS connections.

[Patrick]

---

@PaigeMiller wrote:

---

@Patrick wrote:

@PaigeMiller The usual question is like always: "What's different between the two runs?"
1. Is this running identical code?

2. Are you using the same or different SAS Servers?

3. What's the database?

4. Is it a distributed database with potentially region specific views?

I feel @Tom is onto something here. 

You could via explicit pass-through verify if the data type is the same on the DB side as well as get the db local. 

Then on the SAS side I'd compare again settings like the local. If there are no differences then you would likely need to look into the drivers and the like. 

---

1. Yes

2. No SAS server, this is base SAS 9.4

3. SQL Server

4. I doubt it, but can't say for sure.

Will try passthru

---

There must be a difference either on the Server side (SQL Server), connection (driver) or client side (SAS). 

I'm not aware of any such differences that would cause a SQL Server date column to become Character on the SAS side so I'd also first verify that the data types on the SQL server side are the same using code similar to below:

libname yourlib sqlsrv ....;

proc sql;
  connect using yourlib;
  select * from connection to yourlib
    (
  SELECT *
    FROM 
      INFORMATION_SCHEMA.COLUMNS
    WHERE 
      TABLE_SCHEMA = '<schema>' 
      AND TABLE_NAME = '<table>'
      and COLUMN_NAME='<column>'
    );
  disconnect from yourlib;
quit;

I'd be running this test via SAS using the exactly same libname definition as in the code with the issues to ensure we're accessing the exact same table or view.

[Quentin]

In addition to the question of what database is it, what is the type of the column when viewed with the native database tools?

Further possible testing: If you create a new test table with in the database with a column of the same type and just a few values, can you replicate the problem? Will a new test database on the same server replicate the problem?  Will a test database on a different server replicate the problem?

[PaigeMiller]

Several of you have asked what type of field this is using native data base tools.

It is type DATE, with precision 10.

So does that help at all? How can a different user access this same table and see the field as character and length 20?

[Patrick]

---

@PaigeMiller wrote:

Several of you have asked what type of field this is using native data base tools.

It is type DATE, with precision 10.

So does that help at all? How can a different user access this same table and see the field as character and length 20?

---

It helps because now we know that as per SAS documentation this should map to a SAS numeric column with a Date9. format. 

What engine are you using (ODBC, SQLSRV, ....)?

If your code is identical and you're really connecting to the same table on the same database then the only thing I can think of that could cause this is some issue with a driver.

You could on both machine execute code as below and then compare the SAS logs.

libname yourdb ....;
/* run 1 */
options sastrace='d,' sastraceloc=saslog nostsuffix;
proc sql inobs=1;
  select <problem col> from yourdb.<table>;
quit;
/* run 2 */
options sastrace=',,d,' sastraceloc=saslog nostsuffix;
proc sql inobs=1;
  select <problem col> from yourdb.<table>;
quit;

Such log is likely also what SAS Tech Support will request from you.

SASTRACE= SAS System Option

[PaigeMiller]

The database is SQL Server

I will try these commands.