topic Loading sas date into sql server in SAS Programming

Loading sas date into sql server

chennupriya — Tue, 17 Nov 2015 16:31:05 GMT

Loading SAS Date into SQL server. - Google Groups

I'm trying to load SAS date(format DATE9.,informat 8 int) into sql server database DATE column using ODBC connection.

libname xxx ODBC DSN=xxxxxx bulkload=yes ;

proc sql;
INSERT into sqldb.tablename
select * from xy.sales;
quit;

ERROR: Value 1 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

my sas date format is MMDDYY10.

I appreciate any help on this.

Thanks.

Re: Loading sas date into sql server

LinusH — Tue, 17 Nov 2015 16:40:09 GMT

Are you sure that xy.sales has the exact column order as sqldb.tablename?

options sastrace=',,,d' sastraceloc=saslog;

might reveal any incnsistency between SAS and SQL Server.

Re: Loading sas date into sql server

chennupriya — Tue, 17 Nov 2015 16:43:41 GMT

please check this post

https://communities.sas.com/t5/SAS-Procedures/Uploading-data-to-SQL-server-issue-with-Date-variable/m-p/172158#M44397

I have the same problem as the dates coverting to 1960 when updating a table in sql server from sas

pls help me

Re: Loading sas date into sql server

SASKiwi — Tue, 17 Nov 2015 18:37:58 GMT

What format do you have on your SAS date column and what is the data type of the column you are trying to load the date into?

If you are loading an SQL Server datetime column then the SAS column must have a DATETIME format applied.

Re: Loading sas date into sql server

LinusH — Tue, 17 Nov 2015 19:13:51 GMT

Why do you start a new post?

Not sure what you tried so far.

I created a table in SQL server with date columns
In SQL Server, inserted data into it, with datatime columns casted to date
Queried the data in SQL Server, date columns looks fine
Assigning libname in SAS. date columns appear with date9. format - values looks perfect.
Created a transaction row in SAS, with SAS date values.
Appended the data to the SQL Server table. New date values looks good.

If you have verified that all those steps, and you still have a problem - open a track to SAS tech support.