topic Load odbc temp table with sas dataset in SAS Procedures

Load odbc temp table with sas dataset

NN — Tue, 04 Sep 2012 18:48:45 GMT

Hi All,

I use a libname statement to connect to an odbc .

libname mycc odbc datasrc=myodbc user=myuser password= mypassword;

next i create a temp table in the odbc server using a sas dataset

slow...

poc sql;

create table mycc.'#table1'n as select customer_id from work.sas_table; quit;

The poblem i face is that odbc temp table gets loaded at a speed of 1000 rows a minute.. which is too slow..

any suggestions on how i could increase the loading speed..

Thanks...

Re: Load odbc temp table with sas dataset

SASKiwi — Tue, 04 Sep 2012 19:58:40 GMT

Try adding the INSERTBUFF = option to your LIBNAME. The default value for ODBC is only 1 according to the documentation, that is one row at a time. You may have to experiment to get the optimal value, but I suggest you try values like 500 or 1000. You may also be restrained by the DBCOMMIT = option where usually the default is 1000. Setting this to 0 means only one commit is made at the end of the load, and this may well help also.