DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 98

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

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:

PC.JPG


SAS EG output:

EG.JPG 

Actual SQL table definition:

SQL.JPG

 

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.

PROC Star
Posts: 1,760

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

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

Frequent Contributor
Posts: 98

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

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

PROC Star
Posts: 1,760

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

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; 

 

Ask a Question
Discussion stats
  • 3 replies
  • 94 views
  • 0 likes
  • 2 in conversation