topic Most efficient way to merge large data sets in SAS Programming

Most efficient way to merge large data sets

babu-in — Tue, 23 Apr 2019 12:48:22 GMT

I have several large datasets(30GB), and try to merge only few variables from second table to first table based on key variable. But since they are too big, it will take a long time. which method is most efficient to do this.

Re: Most efficient way to merge large data sets

Kurt_Bremser — Tue, 23 Apr 2019 12:51:59 GMT

Are both tables of this size, or do you have one large table and a considerably smaller one used for a lookup?

Do you have a many-to-one or a many-to-many relationship?

In any case, you need to strive to "travel light", meaning you only keep the variables you need when sorting in preparation for a merge.

Re: Most efficient way to merge large data sets

babu-in — Tue, 23 Apr 2019 13:01:05 GMT

yes, tables are same size and keys are unique in the both tables.

Re: Most efficient way to merge large data sets

Kurt_Bremser — Tue, 23 Apr 2019 13:05:31 GMT

Then take a closer look at your data. See if the datasets are compressed, and see if you need all columns in your output or if you can drop some.

If in doubt, run proc contents on your datasets and post the output.

Also, please post the log of your merge code, using the {i} button (available when posting Rich Text).

Re: Most efficient way to merge large data sets

Patrick — Tue, 23 Apr 2019 13:05:48 GMT

@babu-in

On this level the best answer I can give is "it depends".

Assuming both data sets are SAS tables what normally takes the most time is sorting and writing data to disk. That's either a Proc Sort with a data step merge or then a SQL Join. But also the SQL join will sort and write data to disk as intermediary temporary files in UTILLOC.

"and try to merge only few variables from second table"

IF you can fit these few variables into memory then most likely a data step hash lookup would perform best as this doesn't require any sort operations and reduces write operations to disk.