topic create table on SQL server using SAS in SAS Enterprise Guide

create table on SQL server using SAS

deleted_user — Tue, 01 Aug 2006 14:07:32 GMT

Can I create table on SQL server using SAS?
How?
I like physical create tables, and insert the data.
I can connect using ODBC.
Not sure how to create tables on SQL server using ODBC connection.
Any suggestions?

Thanks

Re: create table on SQL server using SAS

ChrisHemedinger — Thu, 03 Aug 2006 19:51:23 GMT

Hi,

You would need to use SAS/Access to ODBC (or OLEDB) to define a SAS library that maps to your SQL Server database. Then you should be able to create data sets using any of the usual SAS methods (PROC SQL, DATA step, etc) that reside in that library, and therefore the SQL Server database.

Chris

Re: create table on SQL server using SAS

bobyang — Mon, 04 Apr 2011 23:26:14 GMT

but I got problem to load large sas data set (88 million rows) to SQL server.
it alway reaches to 65457 and then stop. there is NOT sas log error or warning. just stuck here and on the SQL server side you see the talbe is NOT growing only has 65457 records.
On the unix sas server side. We have set up the ODBC. INI file correctly and set up a libname as follow in our SAS code and the library has been successfully established.
libname sql_side sqlsvr dsn=ACESQL uid=8FN pwd=XXXXXX schema=dbo ;
we use proc copy to load sas data to sql server, but whenever reach to 65547 and then it stop.
It seemed that someone told me to use a “commit” function or command to let sql server accept and commit the records first and then it can load more?? Please help!!

Re: create table on SQL server using SAS

Ksharp — Thu, 07 Apr 2011 10:25:54 GMT

I think obs 65458 contains some illegal character in that table, So when SAS transcode it fail,SAS will stop read in the obs after it.I meet this situation frequently.
So Check the dataset' s variable attribute. to see whether the length of some variables is
very long (e.g. 32700) or is zero.
You 'd better to open the table to see content to find whether there are some strange character.
Then drop these variables , you will successfully imported.

Ksharp