topic Creating temporary DB table using implicit pass through takes too long in SAS Programming

Creating temporary DB table using implicit pass through takes too long

kc_dn — Fri, 29 Jul 2022 18:40:15 GMT

We use SAS 9.4 on a Linux platform. All Studies share the same MS SQL DB Tables and I only want to overwrite the data for the Study currently being processed. So, I wrote a macro that:

Creates a temporary DB table from the current Study’s SAS dataset.
Deletes the current Study’s data from the permanent DB table.
Loads the temporary DB table into the permanent DB table.
Drops the temporary DB table.

Everything works beautifully. However, since we cannot create a MS SQL Server table from a SAS dataset using explicit pass through SQL, I have to create the temporary DB table using implicit pass through.

PROBLEM: Creating the temporary DB table using implicit pass through takes over 2 hours to load 5 million records. Is there a better way of doing this? Below is my macro.

Any help much appreciated 🙂

Re: Creating temporary DB table using implicit pass through takes too long

Reeza — Fri, 29 Jul 2022 19:23:52 GMT

Rather than PROC SQL SELECT try a PROC DATASETS COPY.

proc datasets lib=atr;
*copy over final to atr library;
copy in=work out=atr;
select final;
*rename final data set to temp;
change final=TEMP;
run; quit;

The SQL step is reading each line one at a time, where COPY will copy in bulk.

If you have the correct SAS license (SAS/ACCESS) you can also try SAS DBLOAD.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0o4e1x3ggqlxan12sokc2lbgxbh.htm

Re: Creating temporary DB table using implicit pass through takes too long

SASKiwi — Sat, 30 Jul 2022 05:08:29 GMT

In addition to @Reeza 's suggestion adding the options UPDATEBUFF and DBCOMMIT to your STR LIBNAME statement can make a HUGE difference in data loading performance. The default values for these options are not very efficient in my experience. You will probably need to experiment with different values to get the best load times. I suggest you try values like 1,000, 5,000, 10,000 and so on, but test it on a subset of your 5 million rows to speed up the testing process.

Re: Creating temporary DB table using implicit pass through takes too long

kc_dn — Wed, 03 Aug 2022 00:32:15 GMT

Thanks! I tried all three values on a million records and it appears DBCOMMIT=10000 INSERTBUFF=10000 provides the most improvement.

NOTE: PROCEDURE DATASETS used (Total process time):
real time 8:56.77
cpu time 18.00 seconds

NOTE: PROCEDURE SQL used (Total process time):
real time 9:15.28
cpu time 17.61 seconds

Re: Creating temporary DB table using implicit pass through takes too long

kc_dn — Wed, 03 Aug 2022 00:34:43 GMT

Thanks! I tested it on a million records and it provides a slight improvement over PROC SQL if paired with DBCOMMIT=10000 INSERTBUFF=10000 as suggested in the other reply.

NOTE: PROCEDURE DATASETS used (Total process time):
real time 8:56.77
cpu time 18.00 seconds

NOTE: PROCEDURE SQL used (Total process time):
real time 9:15.28
cpu time 17.61 seconds

Re: Creating temporary DB table using implicit pass through takes too long

SASKiwi — Wed, 03 Aug 2022 01:10:00 GMT

@kc_dn - Woohoo! That's a massive improvement! Can't complain about loading a million rows in 9 minutes. Bulk loading might improve things further but if you are happy with the current load times I wouldn't bother.