topic Re: SAS migration from Win to Linux (encoding differences) in SAS Data Management

SAS migration from Win to Linux (encoding differences)

sid3284 — Mon, 22 Jul 2019 19:48:39 GMT

Hi,

We are migrating from SAS 9.4 on Windows to Linux operating system.

For one of the DI jobs we see the below difference in the output of the job. This data is being pulled from a SQL server which is on Windows. We are using SAS/Access to ODBC to access data in sql server.

On Windows,

2/13/18
Sarah’s been running around like crazy; discussed the billboard next to Exit 90A, feature in the Sport & Leisure Research Group, and article in Forbes; she’s been busy putting simulators in the Mercedes Benz Stadium due to Arthur Blank’s dem

On Unix,

2/13/18
SarahÃ¢Â?Â?s been running around like crazy; discussed the billboard next to Exit 90A, feature in the Sport & Leisure Research Group, and article in Forbes; sheÃ¢Â?Â?s been busy putting simulators in the Mercedes Benz Stadium due to Arthur B

This is as a result of substring of 250 character of the record. I think the garbage characters are because of encoding mismatch.

Is there any configuration settings that I am missing. Major concern here is data loss because of encoding mismatch.

Re: SAS migration from Win to Linux (encoding differences)

Reeza — Mon, 22 Jul 2019 19:57:37 GMT

The config file specifies the encoding for each system. But...Unix files usually have a different encoding than Windows.

Re: SAS migration from Win to Linux (encoding differences)

SASKiwi — Mon, 22 Jul 2019 20:00:57 GMT

Run PROC OPTIONS on both servers and compare encoding options. Are they the same or different?

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Mon, 22 Jul 2019 20:26:03 GMT

Its WLATIN1 for Windows and LATIN1 for Redhat.

On Redhat we changed the encoding to UTF-8 but that also did not help much.

Re: SAS migration from Win to Linux (encoding differences)

SASKiwi — Mon, 22 Jul 2019 21:01:31 GMT

It would be worth asking the SQL Server DBA what the encoding is for the database. Is it running on Windows? It would be worth opening a SAS Tech support track as well.

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Mon, 22 Jul 2019 21:05:24 GMT

Yes. Its running on windows.

Latin1_General_CI_AI is the collation on the table from which we are reading the data.

Re: SAS migration from Win to Linux (encoding differences)

Kurt_Bremser — Tue, 23 Jul 2019 05:40:15 GMT

Looks to me like somewhere along the way the data is converted to a UTF format, as WLATIN1 uses a single-byte character for the single quote, which should not end up as three(!) special symbols.

Isn't MS SQL always accessed through ODBC?

Re: SAS migration from Win to Linux (encoding differences)

Patrick — Tue, 23 Jul 2019 08:43:26 GMT

Like @Kurt_Bremser observed that you end-up with multiple garbled bytes clearly indicates that somewhere along the line a conversion to UTF-8 must be happening (could just be for a WORK tables).

UTF-8 encoding for a right single quotation mark uses 3 bytes: 0xE2 0x80 0x99 (e28099)

Are the characters already garbled in the very first SAS table directly extracted from the DB or do they only get garbled when inserted to the target table?

Do you fully recreate the target table every single time or have you migrated it from your Windows environment? If migrated - did you use Proc Migrate or did you just copy the physical files? What's the encoding of your target table with the garbled characters?

Does the SAS log tell you anything about encoding changes/problems?

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Tue, 23 Jul 2019 11:55:40 GMT

@Kurt_Bremser @Patrick The SQL server table is converted into a csv file and saved on the Redhat (compute) server. I think this is where it gets converted to UTF-8. echo $LANG on linux box gives en_US.UTF-8.

The target table is in snowflake. But i think the characters are garbled when they are written in the csv file on the unix server.

We are using file statement to write the csv on linux server. Below is the file statement.

file "/sasdata/Snowflake_temp/&sas_libname./&temp_table..csv" delimiter=',' DSD DROPOVER lrecl=32767 ENCODING='wlatin1';

We have tried both wlatin1 and latin1. But we still see the garbled character in the csv.

When I run file -bi on the csv file I get output as

text/plain; charset=unknown-8bit. which makes me think that the ENCODING option in file statement is not picked up.

Re: SAS migration from Win to Linux (encoding differences)

Kurt_Bremser — Tue, 23 Jul 2019 12:03:52 GMT

Have you tried to simply create a WORK dataset from the SQL table, and look at its contents?

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Tue, 23 Jul 2019 12:48:30 GMT

2/13/18
Sarahâ??s been running around like crazy; discussed the billboard next to Exit 90A, feature in the Sport & Leisure Research Group, and article in Forbes; sheâ??s been busy putting simulators in the Mercedes Benz Stadium due to Arthur Blankâ??s

Description

This is how the data looks.

I tried to use OUTENCODING option in the libname statement but it seems it is not supported in SAS/Access to ODBC.

26 LIBNAME srccrm_1 ODBC READ_LOCK_TYPE=NOLOCK READBUFF=1000 DATAsrc=LEGENDS SCHEMA=dbo USER=sassql
26 ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX OUTENCODING ='WLATIN1';
_________
278
NOTE: Libref SRCCRM_1 was successfully assigned as follows:
Engine: ODBC
Physical Name: LEGENDS
WARNING 278-63: The option OUTENCODING is not implemented in the ODBC engine.

Re: SAS migration from Win to Linux (encoding differences)

Tom — Tue, 23 Jul 2019 13:37:20 GMT

Pull the data out of Snowflake with some other tool and check if that character is a normal ASCII single quote or one of those typography slanted quotes.

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Tue, 23 Jul 2019 14:21:41 GMT

@Tom the character is

146

2019

’

Right Single Quotation Mark

https://www.i18nqa.com/debug/table-iso8859-1-vs-windows-1252.html

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Tue, 23 Jul 2019 14:24:15 GMT

@Kurt_Bremser @Patrick Is there any other way I can set encoding in ODBC to write data as WLATIN1.

Is this available in SAS/Access to SQL Server?

Re: SAS migration from Win to Linux (encoding differences)

Tom — Tue, 23 Jul 2019 14:49:41 GMT

Did you change the database that you are querying in addition to changing your SAS server? Somehow the 3 byte UTF-8 sequence for cute curly apostrophe got stuff into your database in place of the simple ASCII single quote. You should check how that table was created in the database.

Anyway it looks like Snowflake only supports Unicode (aka UTF-8).

You might try transcoding the string after copying from the database. But since it is a single byte encoding WLATIN1/LATIN1 will NOT be able to represent every possible Unicode character that might appear in your database.

Try the UNICODE() function.

https://documentation.sas.com/?docsetId=nlsref&docsetTarget=p1s61kmqg61m29n109i6dz4ivkbw.htm&docsetVersion=9.4&locale=en

Re: SAS migration from Win to Linux (encoding differences)

Patrick — Tue, 23 Jul 2019 15:10:19 GMT

@sid3284

Can I suggest that you set up a very simple step run out of SAS EG (or eventually DIS).

proc sql;
   create table test as
   select mycol
   from db.source
   where <selection for specific row>
  ;
quit;

Then execute this code both in your old and in your new environment.

From what I understood so far transcoding into WLATIN1 in your old environment didn't cause any issues. If so then trancoding into LATIN1 should principally also be possible and it's only about getting the environment right.

If you've got the SAS/Access to SQL server module licensed in your new environment then I'd use this one over SAS/Access to ODBC - just because it's the specialised one and I believe comes together with a SAS tested ODBC driver.

Can you please run above simple tests and then tell us the results. We would also need to know the SAS session encodings, the target table encoding and the local.

I'm not sure if the following link could still be of relevance (I hope not) - but it's eventually another reason to use SAS/Access to SQL Server. http://support.sas.com/kb/36/652.html

As for your questions around available options for the Access modules: That's fully documented here per SAS access module.

https://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=titlepage.htm&docsetVersion=9.4&locale=en

Re: SAS migration from Win to Linux (encoding differences)

Patrick — Tue, 23 Jul 2019 15:20:19 GMT

And just as another test: What happens if you set the target table encoding specifically in your RHEL server environment?

proc sql;
   create table test(encoding=latin1) as
   select mycol
   from db.source
   where <selection for specific row>
  ;
quit;

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Tue, 23 Jul 2019 15:32:33 GMT

@Tom We are reading from SQL server using SAS/Access to ODBC. We extract that data into a temporary work table on linux server and I think the data fetched in this dataset got the data converted from WLATIN1 to UTF-8. We use this temporary work dataset to create a csv and move that to s3 bucket and load it into snowflake.

We are looking for an option to maintain the same encoding on the temporary dataset from SQL server.

Re: SAS migration from Win to Linux (encoding differences)

Tom — Tue, 23 Jul 2019 16:03:55 GMT

Converting from WLATIN1 to UTF-8 is not going to create that curly apostrophe thing. That is not a character in the WLATIN1 encoding.

Most likely the source is the SQL Server database. Check the value by querying there.

You will probably have a lot more luck by running your SAS sessions using UTF-8 encoding all of the time. So use UTF-8 session when reading from SQL Server and writing the text file. Snowflake will use UTF-8 when loading (if not check that). Then use UTF-8 session when reading from Snowflake and the values should look the same as they did in the UTF-8 session that read from SQL Server.

Actually I am wrong. '91'x to '9B'x appear to be where it is storing a lot of those goofy characters.

217   data _null_;
218     do x='91'x,'92'x;
219       put x= ;
220     end;
221   run;

x=‘
x=’

Not clear to me why your connection to Snowflake is not converting those back into '92'x instead of a three byte sequence.

Your original kind made it look like something in the pipeline is doing the WLATIN1 to UTF-8 mapping twice. Instead of just 3 weird characters it looks like you got 9. So something translated the non ASCII code '92'x into multiple bytes. Then something else translated each of those multiple bytes in other multiple byte sequences.

Does Snowflake expect a BOM at the beginning of the the text files that it loads? From one of you other messages it looks like your SAS export code did NOT write a BOM so that readers of the text file would know what encoding it had.

Re: SAS migration from Win to Linux (encoding differences)

sid3284 — Tue, 23 Jul 2019 18:00:53 GMT

@Patrick

Redhat server output:

"2/13/18
Sarahâ€™s been running around like crazy; discussed the billboard next to Exit 90A, feature in the Sport & Leisure Research Group, and article in Forbes; sheâ€™s been busy putting simulators in the Mercedes Benz Stadium due to Arthur Blankâ€™s demands; discussed The First Tee, Steve Mona and a handful of other individuals who may get her across the finish line; $$$ are still small locally; product licensing would be an issue as well (i.e. PXG); sheâ€™ll be at game tomorrow with family and 25 other people so hope to get some face time then

Misc: Orlando is home to one of PGA TOUR Superstore's newest locations. The retail side of the golf industry has changed significantly in recent years and no company in the game has adjusted and evolved more successfully than PGA TOUR Superstore. Mirroring the approach that helped Home Depot change the home improvement sector, the retailer is coming off a record year in which it had 23 percent sales growth, opened several new stores, conducted over 100,000 custom club fittings, gave about 50,000 lessons and re-gripped more than 750,000 clubs. â€œA lot of retail companies, theyâ€™re still playing with the old set of cards, so to speak,â€ says PGA TOUR Superstore President and CEO **bleep** Sullivan. â€œWeâ€™ve reshuffled the deck. Weâ€™ve revolutionized, just as Home Depot revolutionized the home improvement business, and itâ€™s more than just selling products.â€ Thereâ€™s good reason PGA TOUR Superstore has applied the same approach and business practices as Home Depot: company owner Arthur Blank co-founded Home Depot. Yes, PGA TOUR Superstore boasts a very wide variety of products, the newest of which will be introduced this week at the annual PGA Merchandise Show in Orlando. The company also offers a range of services â€“ fittings, re-gripping, lessons and hitting bays â€“ and relies on experienced associates to provide a level of customer service that keeps visitors coming back. â€œWe did that at Home Depot; the product knowledge we had on the floor"

Windows server output attached.