Efficient way of exporting SAS data sets to SQL server DB with OLE DB

[ashanti108]

I have used the code below to load SAS datasets to a SQL Server table. The problem is that it took more than 10 hours to load this 80 million records with 12 columns.
The SAS data set and the SQL server databse reside on different servers.
Is there a better or efficient way to load this data to SQL server ?



libname DW99 oledb datasource="DW67" provider=sqloledb properties=('initial catalog'=DMV 'Integrated Security'=SSPI) schema=dbo;

proc sql;
connect to oledb(datasource="DW67" provider=sqloledb
properties=('initial catalog'=DMVRAD 'Integrated Security'=SSPI));
execute(truncate table Death) by oledb;
disconnect from oledb;
quit;

proc sql;
insert into dw99.Death(SASDATEFMT=(SSA_DOB='MMDDYY10.' SSA_DOD='MMDDYY10.' SSA_Update='MMDDYY10.' Record_Thru='MMDDYY10.'))
select * from one
quit;
libname dw99 clear;
PROC DATASETS LIB=WORK KILL;
QUIT;
run;

 

[klaz2002]

This may be a function of your SQL server. 80 Million records is pretty large. While 10 hours sounds long, you may have gigs of data that must be inserted into the database. Each insert must make a few inserts into system tables as well. Have you tried SQL's own DTS package/ The idea is to create an import script (via the wizard) and see how long that process runs.

Klaz

 

[ashanti108]

I thought about using the SSIS or DTS but there is no easy solution. Let me know if you know how to import SAS data set using SSIS package or DTS..

Thanks