topic Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86)) in SAS Programming

SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

nondescript — Sun, 26 Jul 2020 15:00:11 GMT

SAS Version: 9.4 32bit (9.4 TS Level 1M5 | W32_7Pro platform)

ODBC Redshift Driver: Amazon Redshift (x86) | 1.04.14.1000

Issue:

When opening a Redshift table in the SAS Explorer, SAS looks like it's running, but ultimately freezes and I need to right click and close SAS.

What is not an issue:

I can query of the redshift tables just fine, pull data into a work dataset and view the info there. I can also view the columns of the redshift tables.

Things I've tried / configurations:

1. Modified my SAS Connection

libname x odbc dbmax_text=250 readbuff=250 noprompt= "Driver={Amazon Redshift (x86)};
Server=NONDESCRIPT_SERVER;  
Database=NONDESCRIPT_DB; UID=NONDESCRIPT; PWD=NONDESCRIPT_PWD; Port=5439"  schema=NONDESCRIPT_SCHEMA;

I am able to connect to redshift , I can see the tables, I can right click and view the columns, but I'm unable to open the tables.

The default field lengths were $1024 - using the dbmax_text I shrunk down to 250. Did not solve the issue.
Added readbuff = 250. Did not solve the issue

2. Modified the ODBC Driver

Using the Additional Options I selected "Use Declare/Fetch Caches Size: 100". The previous selection was "Retrieve Entire Result Into Memory"

3. Changed ODBC Drivers

Amazon Redshift (x86) - My primary but still doesn't resolve the issue.
DataDirect 8.0 Amazon Redshift Wire Protocol - doesn't resolve the issue.
PostgreSQL Unicode - doesn't resolve the issue.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

ballardw — Fri, 24 Jul 2020 15:27:03 GMT

This is guess that has a chance of helping: Set options validvarname=Any;

before the libname statement. If the problem stems from possible column headings with non-standard characters that SAS doesn't use, anything except letters, digits and the _ character, this may help.

If so you will need to reference such columns in name litteral form, quotes around the text followed by an n to tell SAS it is a name. Example: "Var with * in name"n

If this is the case I would suggest copying the data sets and then renaming variables as the literals get ugly to work with quickly. You could assign labels to the new variables with the original text.

Also, SAS will not allow longer than 32 character variable names and this might help with that as well. Might.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

Tom — Fri, 24 Jul 2020 16:06:04 GMT

What do you mean by "open the table"? Are you trying to browse the data in some interactive window? Or are you running some SAS code to use the data?

If you are trying to browse the data it might well be trying to copy the whole table from Redshift into your SAS server. Depending on the size of the table that might take a long time.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

nondescript — Fri, 24 Jul 2020 16:16:42 GMT

I'm trying to browse the data via the SAS Explorer window.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

nondescript — Fri, 24 Jul 2020 16:58:43 GMT

Thanks for the response, @ballardw - unfortunately adding options validvarname=Any; didn't work.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

SASKiwi — Sat, 25 Jul 2020 00:06:24 GMT

I assume you are using SAS installed on your PC given you say it is 32-bit. What maintenance release of SAS 9.4 are you using? Support for Amazon Redshift is fairly recent in SAS so if you are not on the latest maintenance release 9.4M6 then you should consider upgrading, along with switching to 64-bit SAS which will give you a lot more memory to use.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

ChrisNZ — Sat, 25 Jul 2020 09:20:55 GMT

What happens when you create a SAS data set with say 1000 rows?

data TEST;

set X.TAB(obs=1000);

run;

Any messages in the log?

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

Tom — Sat, 25 Jul 2020 17:04:59 GMT

@nondescript wrote:
I'm trying to browse the data via the SAS Explorer window.

So you are using Display Manager? (instead of Enterprise Guide or SAS/Studio)

Don't do that with large datasets. I don't think the ViewTable tool is smart enough not to trigger moving the whole dataset from Redshift into your SAS computer.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

nondescript — Sun, 26 Jul 2020 14:50:29 GMT

Hi @ChrisNZ

I am able to retrieve data just fine as well as view the data- in fact, it seem quite a bit faster than oracle tables I normally query off. This is good news and I will update my post.

However, it does still freeze when trying to view the dataset sitting in Redshift.

Thanks

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

nondescript — Sun, 26 Jul 2020 14:53:33 GMT

Hi @SASKiwi

I'm using SAS 9.4 TS Level 1M5

W32_7PRO.

I'm not too familiar with the maintenance, patches, and updates of SAS so any recommendation is greatly appreciated.

Thank you

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

SASKiwi — Sun, 26 Jul 2020 20:05:28 GMT

9.4M5 is quite recent. I'd suggest trying this on another PC if possible just to confirm the behaviour is consistent and also raising this with SAS Tech Support.

Re: SAS Freezes when opening Redshift Table (9.4 - Amazon Redshift (x86))

ChrisNZ — Sun, 26 Jul 2020 23:03:14 GMT

One for tech support I reckon.

There is no reason that redshift should behave differently than other external sources.

They may have ways to look at why this is happening.

In the meantime, you have a workaround, and maybe a way to see of this behaviour is nor triggered by a specific record with weird data.