Loading sas date into sql server

[chennupriya]

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.

[LinusH]

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.

[chennupriya]

please check this post 

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

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

pls help me 

[SASKiwi]

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. 

[LinusH · Accepted solution]

Why do you start  a new post?

Not sure what you tried so far.

1. I created a table in SQL server with date columns
2. In SQL Server, inserted data into it, with datatime columns casted to date
3. Queried the data in SQL Server, date columns looks fine
4. Assigning libname in SAS. date columns appear with date9. format - values looks perfect.
5. Created a transaction row in SAS, with SAS date values.
6. 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.