topic Re: Encoding problem with SAS-job that reads from Netezza and writes to SQL Server in SAS Programming

Encoding problem with SAS-job that reads from Netezza and writes to SQL Server

EinarRoed — Thu, 14 Jan 2021 06:37:23 GMT

I've got a SAS-job that extracts data from multiple sources, including Netezza. It runs successfully when I execute it in Enterprise Guide.

However when this program is executed via the daily batch, all special characters (notably æ, ø, å) that are loaded from Netezza into the target table appear like '?'.

The daily batch executes the SAS-job via SSIS. The target table is on SQL Server.

Do you have any advice on how I can resolve this issue? I want the special characters to appear as they are. Do I need to include some kind of encoding logic in the SAS-job?

Re: Encoding problem with SAS-job that reads from Netezza and writes to SQL Server

AnandVyas — Thu, 14 Jan 2021 07:23:45 GMT

Hi @EinarRoed

You can start by verifying if SAS session encoding value is same when executed from EG and via batch.

Below proc can fetch this information in SAS log.

proc options option=encoding; run;

Re: Encoding problem with SAS-job that reads from Netezza and writes to SQL Server

EinarRoed — Fri, 15 Jan 2021 12:19:17 GMT

Thanks! The business user is going to insert it into his script and make the comparison.

In the meantime there's a bigger problem.

We know that the SAS-script executed in the daily batch uses LATIN-9 (ISO-8859-15) encoding. We also know that the SQL Server target database uses "Danish_Norwegian_CI_AS" encoding/collation. Yet all occurrences of ÆØÅ from the various sources appear as '?' in SQL Server.

Is there a kind of conflict/inconsistency between how LATIN9 and "Danish_Norwegian_CI_AS" treats ÆØÅ? If so, I'd appreciate any suggestions on how to resolve it. I assume we'll need some kind of encoding logic in the SAS-script.