Export sasdat to SQL Server

[annisamalik_]

Hallo, please kindly help me. I want to export my sasdat to SQL Server, but why it tooks too much time. I want export my data (8million rows) and it takes time 14hours. How to solve it? How to make my SAS run fast? Thanks

[ChrisNZ]

See the insertbuff option

[s_lassen]

With that kind of data, you will probably want to look at the BULKLOAD option for creating data. Apparently the BULKLOAD option is only available if you use ODBC to connect to the data - and the ODBC functionality may be a bit too simple in some cases - I have once or twice been forced to create the bulkload file myself and use the command-line interface to SQL server (BCP command) to load the data. It is also possible to use the BULK INSERT command in SQL Server (use pass-through SQL), which you can initiate using pass-through SQL, I think the functionality is similar to the BCP command.

If you are using ODBC, and you can live with how the data comes out, that is probably the easiest, just specify e.g.:

libname sqlserv ODBC <connect options>;

data sqlserv.out_table(bulkload=YES);
  set <sas dataset>;
run;
  

Another possibility is to increase the value of the INSERTBUFF dataset (or libname) option. Not as fast as bulk loading, but it is still a good way of increasing throughput.