BookmarkSubscribeRSS Feed
zqkal
Obsidian | Level 7

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?

Or

Has a macro to submit 1000 rows one by one.

Thanks for your time in advance.

6 REPLIES 6
Reeza
Super User

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.

zqkal
Obsidian | Level 7

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


Reeza
Super User

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.

Ksharp
Super User

Try libname's option  readbuffer=1000  to speed

SASKiwi
PROC Star

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.


zqkal
Obsidian | Level 7

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1393 views
  • 3 likes
  • 4 in conversation