SAS migration from Win to Linux (encoding differences)

[Tom]

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.

[Patrick]

@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&loca... 

[Patrick]

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; 

[sid3284]

@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.

[Kurt_Bremser]

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?