Hello @Manny3,
as said, from now on, and until to steer up properly, you will need to be very careful of the encoding and formats used in your SAS process (your sasv9 configuration) abd in your origin and destination data sources.
So, as long as you won;t change encoding anymore, you will know the encoding of your SAS process: UTF-8/UNICODE.
Your data source / ODBC, you will need to know, on each database, what is the encoding and format used.
For your data sets created, they will be on the encoding and format as your SAS session. Hence, perhaps you are creating sas data sets with different encodings: the ones on latin9 as on your previous environment, then utf-8 as after the upgrade, then others in latin9 again after tyour previous change, and, again, other datasets in utf-8 after your latest change of configuration.
Procedure:
I trully think the first thing you need to do is to align all your SAS data sets to a single utf-8 encoding. All fo them. So, first, get a proper list of tables + table encoding (proc contents)
Then change your latin encoded SAS tables to utf-8 http://support.sas.com/kb/15/597.html
Finally, you will need to find the way that adapts to your requirements, to query and join data from your ODBC sources to your SAS tables (and the other way around). This will be a mix of:
Adapting your current code to use/translate the current to the characters set from origin and destination tables, and modify functions such as trim.
Having another Application Server (on the encoding adaptable to your ODBC sources). With this session, you will need to create filtered copies of your ODBC tables to SAS tables.
With the Application Server (on UTF-8 encoding), repeat step 2 - change SAS tables to UTF-8
Now that you have all your tables in SAS in UTF-8, you actually run your code (adapted).
If you want to push data back to ODBC sources, you will need to do the inverse patch 3.3 to 3.1)
PS.
Check this out as well: https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/SAS9UTF-8SupportandTroubleshootingTips.pdf
This might be of your interest too:
Problem Note 36652: Some characters might not render correctly when data is read from the Microsoft SQL Server database to a SAS® Unicode session
http://support.sas.com/kb/36/652.html
However, a SAS Unicode session might not render correctly any non-Unicode character data from Microsoft SQL Server. If the operating system's locale and the database locale are the same, the ODBC driver does not transcode the data, and SAS cannot determine the encoding.
To work around the issue, correct the encoding attribute of the SAS data set that is created from the database to convert it to session encoding
... View more