@freshstarter
I wonder why you are trying to compare data in SAS!?
You Data Migration team is responsible for ensuring the integrity of the data contents are being preserved between the two underlying systems (SQL Server vs. Snowflake).
SAS software is a consuming Third-party application, no different than any other software that can consume data via ODBC driver provided by SAS Institute or some other vendor.
One other factor to keep in mind - https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/nlsref/p00fhxufzmc274n19xax2hllvin4.htm
"If your data set encoding does not match the encoding of your SAS session, the character data in your data set might need more space in the new encoding. For example, if your SAS session is using a UTF-8 session encoding, and you are reading a data set with an encoding of Windows cp 1252 (WLATIN1), some of the characters that require one byte in WLATIN1 might require 2 or 3 bytes in UTF-8. To avoid data truncation, your character variables must expand to a width that is 1.5 times greater than the width of the original data."
It's very important to understand the difference in how SAS represents character lengths vs. how a Database does.
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p02d1rki3en24vn105nmjsa6k786.htm
- Typically Snowflake uses character length, where SAS would use Byte length to properly store and present character values.
i.e. if the length of a column in Snowflake table is defined as varchar(3). SAS might need to define it as char(9) in order to ensure Multi-Byte characters are properly preserved.
These differences in Column length definitions and how data is extracted and stored in SAS data sets will cause lots of inconsistencies in SAS Table definitions/structure, as well as actual values stored within the columns (trailing spaces and padding)
With all that said, if you must use SAS software to compare, then I would recommend
Use SAS UTF-8 Binaries <SASHome>/SASFoundation/9.4/bin/sas_u8
Setup your Snowflake & SQLServer Libname statements with the correct NLS related : LIBNAME and Other Options for NLS
IF you are extracting data from the database and storing as SAS data sets for comparison, then store the extracts in different paths/directories
Use CVP engine for your Latin1 extracts (SQLServer) https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p1ml1gmtqm3h72n17yjxbyagw0mm.htm
Ignore column lengths differences and focus on comparing Columns Types and Stripped character values.
Sorry for the lengthy reply, but I wanted to provide a context that may not be familiar to you or other readers. Hope this helps
... View more
