×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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;
 

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

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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close