Reading Unicode from Snowflake into SAS via ODBC

[AndrewZ]

Yes, I checked this is SAS Unicode, then double checked, triple checked, and then checked a few more times.

Here is a screenshot

[Quentin]

---

My setup is similar, except I'm running 9.4M7, and 64-bit SAS on Windows, so I'm using the 64-bit ODBC driver from snowflake.

@AndrewZ wrote:

Yes, I checked this is SAS Unicode, then double checked, triple checked, and then checked a few more times.

Here is a screenshot

---

[AndrewZ]

Our organization has been on Snowflake since 2020, and we use both 64-bit and 32-bit SAS, so we have tested many combinations of SAS versions, driver versions, and bitness, but Unicode never worked once with SAS and Snowflake.

[artisan88]

This is an old thread, but our org recently ran into the same troubles and could find nothing online except this thread, which still appeared to be inconclusive. So after several go-arounds with SAS Support (over the course of a few months and incorrect suggestions by SAS support like using a CAST statement in the passthrough call...), they confirmed this is indeed a SAS ODBC problem that is, according to them, only fixed in M9 with SAS/ACCESS to Snowflake (so, not in the ODBC call there either). So, we took a workaround that others may find useful: we have SAS pass through the command to Snowflake put the table's UTF8 CSV to an external stage, then download that with a pre-signed URL via proc http, then proc import. There are some gotchas you need to account for like if the table is blank, Snowflake won't write a "header-only file" (so you have to manually force writing such a thing in that scenario), deal with line breaks and data formats, but it's quite possible and still performant for our data volumes.

[Tom]

The lack of a header row shouldn't be a problem.  In fact you probably don't need (or want) the header row.  Instead just define the variables in the proper order, say by setting the original table.

data want;
  if 0 then set snow.mytable;
  infile 'myfile.csv' dsd truncover;
  input (_all_) (:);
run;

You might need to add an INFORMAT statement for date or datetime variables, depending on how you had SNOWFLAKE write them into the CSV file. Make sure the INFORMAT statement is AFTER the SET statement so it doesn't change the variable order.

[AndrewZ]

@artisan88 

Thank you for your post.

As a follow up to my earlier post, nothing has changed: SAS with ODBC continues to not work with Unicode. Your workaround to use pass-through makes sense, and I considered doing something like that.  

For speed reasons, I already set up a similar system to bulk load from SAS to Snowflake, but it supports Unicode too. I make a local TSV or JSON file. Then I use a pass-through with PUT to load it into Snowflake, then make a normal remote table. It sounds like what you do, but in reverse, but I do not use a pre-signed URL or PROC HTTP.

I worked through my own quirks, like mapping the data types, chunking large files, adding a count verification step, a bypass option for tiny tables, and options to either create or append a table.

For my jobs most affected by SAS's Unicode issues with Snowflake, in the future I will be moving them to either use Python or by keeping processing within Snowflake.

[SASKiwi]

@AndrewZ - SAS have just released a new version of the SAS Snowflake ODBC driver for which there is a hot fix available. The previous SAS-supplied driver is no longer supported by Snowflake so it might be worthwhile testing this fix. 

[AndrewZ]

SASKiki: thanks for the tip, but our license is for "SAS/ACCESS Interface to ODBC" which I think doesn't cover a Snowflake-specific connector. Instead, it's a general ODBC connector that we also use for Microsoft SQL, MySQL, Excel, Access, etc.

[SASKiwi]

@AndrewZ  - This is indeed the Snowflake driver used for the SAS/Access Interface to Snowflake. It should have posted this link: https://tshf.sas.com/techsup/download/hotfix/HF2/M3H.html#M3H004