Proc SQL Completes, but Redshift Errors Out on Memory Full Error

[Pretti]

We are using explicit SQL pass through statements to query a large table in Redshift. The Proc SQL completes without a SAS error but has only  about half the number of records we were expecting. Our DBA reviewed the Redshift database log and found the query had an out of memory error and stopped fetching additional rows from the table.

Since these are production Jobs we need to have the SAS Proc SQL Step fail with an error as we do not want to continue the process with an incomplete data set. Additionally, since this is a "silent" error, our users would never know there was problem.

Does anyone know how to pass this Redshift error down to our SAS job?

Thanks.

[Tom]

Are you using SAS/Access to Redshift?  Or SAS/Access to ODBC?

You might want to raise a support ticket with SAS support.

[Reeza]

Agreeing with Tom, this is a question for SAS tech support for sure.

[Pretti]

I first contacted SAS support and the response was to work with our DBA to optimize our queries to get them to run with the size of our memory.  This won't work as the database is shared resource and the total amount of available memory at any given time will be unknown.  I'll continue to experiment to see if I can find a solution.

[Reeza]

Have you tried implicit SQL queries instead? Do those error out as indicated?