DATA Step, Macro, Functions and more

Loading sas date into sql server

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 125
Accepted Solution

Loading sas date into sql server

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.

Accepted Solutions
Solution
‎11-17-2015 03:12 PM
Super User
Posts: 5,429

Re: Loading sas date into sql server

Posted in reply to chennupriya

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.

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 5,429

Re: Loading sas date into sql server

Posted in reply to chennupriya

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.

Data never sleeps
Frequent Contributor
Posts: 125

Re: Loading sas date into sql server

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 

Super User
Posts: 3,252

Re: Loading sas date into sql server

Posted in reply to chennupriya

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. 

Solution
‎11-17-2015 03:12 PM
Super User
Posts: 5,429

Re: Loading sas date into sql server

Posted in reply to chennupriya

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.

Data never sleeps
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 948 views
  • 0 likes
  • 3 in conversation