All Forums Connectivity
cochetti 4 posts Joined 06/13
28 Jan 2014
SAS via OLEDB and Null Values

I am trying to load a small SAS table into Teradata via SAS/ACCESS connectivity  & OLE DB.
The table is mostly NULL and consists of: id, SiteA, SiteB, SiteC, SiteD.  The Site variables are populated with "Yes", "No", or SAS NULL ''.  Only one column is populated at any time.  Therefore the data looks as follows: 

Id       siteA     siteB     siteC     siteD
123      yes  
234                 no
345      no
456                           yes

 When this is transferred to Teradata, it comes out garbled as follows:

Id       siteA     siteB     siteC     siteD
234      yes       no
123      yes
456      nos       no        yes
345      no        no

I have tried the NULLCHAR= , NULLCHARVAL= and DBNULL = options and have not seen an impact.  It appears that the buffer isn't getting fully cleared before moving on to the next row as you can see that id = 456 has retained the "no" and the 's' from "yes" from the previous entries.
Has anyone seen anything like this before?  Does anyone know a solution?
Below is the code I was currently using but many options have been tried, including proc SQL;

libname TERADATA oledb Provider=msdasql dsn=datacore pwd='pass' uid=username schema=USERNAME;

proc sql;
 drop table teradata.patient_test;

data work.test;
input idcode $20. site_1 $5. site_2 $5. site_3 $5. site_4 $5.;
123                 yes                 
234                      no             
345                 no                  
456                           yes       

data teradata.patient_test;
 set work.test;


You must sign in to leave a comment.