topic Macro to extract data from Oracle in SAS Programming

Macro to extract data from Oracle

zqkal — Fri, 10 May 2013 19:42:51 GMT

I have a sas data set with 1 million rows. I need to join to oracle tables but, because of oracle 1000 rows limitation on join condition I wasn’t able to extract the information from oracle.

I tried loading it into oracle temp table and do the join inside oracle. However, it takes a lot of time to load the table.

Does anyone has any suggestions?

Has a macro to submit 1000 rows one by one.

Thanks for your time in advance.

Re: Macro to extract data from Oracle

Reeza — Fri, 10 May 2013 19:48:09 GMT

The oracle limitation is over 1000 columns not rows, can you clarify what you're trying to do. It would be better to not have 1000 columns in general.

Re: Macro to extract data from Oracle

zqkal — Fri, 10 May 2013 20:25:38 GMT

Reeza, I'm trying to join SAS table with 1 million rows to Oracle table.

Re: Macro to extract data from Oracle

Reeza — Fri, 10 May 2013 22:24:56 GMT

The issue is that to join information between sas tables and a server SAS has to bring all the information in.

Depending on what you're trying to do, using a format, a hash table or uploading the table are options. You can search on here, some people have suggest bulkupload options that are faster but I don't know much about that.

Re: Macro to extract data from Oracle

Ksharp — Sat, 11 May 2013 02:51:34 GMT

Try libname's option readbuffer=1000 to speed

Re: Macro to extract data from Oracle

SASKiwi — Sun, 12 May 2013 23:10:02 GMT

How many rows are in your Oracle table? How long does it take to upload your SAS table to Oracle?

I've been able to speed up the loading of Oracle tables by tweaking the INSERTBUFF and DBCOMMIT options (have a look at the documentation for these). You will need to experiment to get the optimal values.

libname upload oracle user=testuser password=testpass path='voyager' dbcommit=10000;

data upload.sastable;

set sastable;

run;

Bulkloading should do it even faster.

Re: Macro to extract data from Oracle

zqkal — Fri, 17 May 2013 16:40:56 GMT

Thanks everyone for your suggestions. I was able to upload the sas data set using DBLOAD faster.