Same SQL field shows different data type in SAS PC & EG

[Dreamer]

Hi,

Same SQL table field shows different data types in SAS PC & EG.

I ran same PROC CONTENTS in PC & EG environment and here is the output. Check for DATADATE field:

PROC CONTENTS DATA=DB_NAME.TABLE_NAME;RUN;

SAS PC output:

SAS EG output:

 

Actual SQL table definition:

Because of this data type mismatch I'm not able to append data to this table from SAS PC.

WARNING: Variable DataDate not appended because of type mismatch.

[ChrisNZ]

Show us the libname statements creating library DB_NAME for both environments (EG and proper SAS).

[Dreamer]

Hi @ChrisNZ,

I have same library statement in both environment:

libname DB_NAME odbc dsn='PC_ODBC_DSN_FOR_THIS_DATABASE' uid= "&UID." pwd="&PWD." schema="dbo";

libname DB_NAME odbc dsn='UNIX_ODBC_DSN_FOR_THIS_DATABASE' uid= "&UID." pwd="&PWD." schema="dbo";

[ChrisNZ]

It looks like the ODBC driver differs in both environments.

SAS will use whatever ODBC gives it. You can use another ODBC client to confirm this.

You can use the cat() or vvalue() function to avoid reading errors when the type is uncertain:

data T1; D='01jan2016'd; format D date9.; run;

data WANT1; set T1; DATE=input(vvalue(D),date9.); run;

data T2; D='01jan2016'; run;

data WANT2; set T2; DATE=input(vvalue(D),date9.); run;