Using PROC FORMAT to help reduce run times when using a LEFT JOIN with much larger RIGHT TABLES.

jbilenas — Sun, 04 Jun 2023 21:13:13 GMT

I was reading a May 10^th, 2023 SAS-L¹ email digest about issues with running mergers with very large data sets (or tables). An email was sent about an individual having long run times with mergers and hash joins and wanted to see how to speed up mergers taking 6 hours or more and HASH joins taking 8 Hours.

From my experience, there are many issues over the years where Data Mergers and/or SQL joins were taking too long usually; sometimes 2 or more days of sorting and merging 2 files. My correction for the 2-day run using USER FORMATS resulted in a 10-minute merge. A common issue in processing epidemiology and consumer data analysis. Using the USER FORMAT reads the larger file without having to sort it and creates a smaller large file that can then be sorted in quicker time.

I have posted a LinkedIn post that showed some examples of this issue and added a few references on PROC FORMAT:

https://jonasbilenascom.wpcomstaging.com/wp-content/uploads/2023/05/Using_SAS_User_Formats_for_Large_Data_Mergers.pdf

Re: Using PROC FORMAT to help reduce run times when using a LEFT JOIN with much larger RIGHT TABLES.

Tom — Sun, 04 Jun 2023 22:25:22 GMT

If the reason for the joins is a simple code/decode lookup then formats (or informats) should always be considered. And if the mapping is many to one (for example age ranges) then formats should require less memory than a hash object that would required exact matches.

topic Using PROC FORMAT to help reduce run times when using a LEFT JOIN with much larger RIGHT TABLES. in SAS Software for Learning Community

Using PROC FORMAT to help reduce run times when using a LEFT JOIN with much larger RIGHT TABLES.

Re: Using PROC FORMAT to help reduce run times when using a LEFT JOIN with much larger RIGHT TABLES.