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

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

Dreamer — Mon, 06 Nov 2017 19:17:55 GMT

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.

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

ChrisNZ — Tue, 07 Nov 2017 01:11:35 GMT

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

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

Dreamer — Tue, 07 Nov 2017 19:32:00 GMT

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

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

ChrisNZ — Wed, 08 Nov 2017 02:51:11 GMT

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;