Forcing a SAS-session to run with encoding 'WLATIN1' (locally and in batch)?

[EinarRoed]

We've got a SAS-script that writes data to SQL Server tables. Unfortunately all 'danish characters' from the source data sets become lost in translation, appearing like gibberish in SQL Server (which uses 'Danish_Norwegian_CI_AS' collation).

The SAS-session insists on running with 'LATIN9' encoding. We'd like to test running it with 'WLATIN1' encoding instead (locally and in batch).

We'd appreciate any advice on how to do this.

[singhsahab]

With the help of encoding="wlatin1" data step options, you can try…

data want(encoding="wlatin1");
set have;
run;

[ChrisNZ]

> The SAS-session insists on running with 'LATIN9' encoding.

This option can only be set at startup, and comes from your configuration file.

If  @singhsahab 's idea

data SQLSVLIB.TARGET_TABLE(encoding=wlatin1); set SASTABLE;

doesn't work, you can try the inencoding= and outencoding= libname options. I think they don't work with ODBC but might be supported by the native SAS engine.

Otherwise, you need to change your config file (or you can add an option to the command if running SAS on the desktop).

[ChrisNZ]

There is no reason to use latin9 for you. It is not meant for Danish, so you should probably change to latin1 once and for all by updating the SAS configuration. The latin1 character set only lacks character ǿ for Danish, which is probably fine.

The existing data sets will still use the latin9 encoding though. They can still be utilised with a performance hit, or can be recreated for optimal performance and message-free logs.

The way of the future is UTF-8, and both your SAS and SQL admins should probably consider moving there. 

[Ksharp]

Your sas session is running under SAS Server like SPDS ? or just local PC version SAS ?

[Ksharp]

Your sas session is running under SAS Server like SPDS ? or just local PC version SAS ?

[EinarRoed]

Thanks for all the feedback! Unfortunately the problem persists. Here's a summary:

• Our SAS version is 4.903.
• Our input is a regular SAS data set. It contains characters like 'æ', 'ø', and 'å'.
• Our SAS-program runs using encoding LATIN9. The characters appear fine there.
• Our SAS-program uses ODBC for writing to a SQL Server database. The ODBC uses the "SQL Server Native Client 11.0" driver.
• The SQL Server database uses "Danish_Norwegian_CI_AS" collation, and nvarchar variables. The special characters appear like "�" there.

We've tried using the encoding option in the data step to set "wlatin1" or "utf-8" encoding. We've also tried setting those in the libname outencoding option. There's no apparent difference.

We've been testing this both locally and via batch. The result is the same. I don't know if the server is SPDS or not, but I doubt it, it's quite old. Let me know if there's an easy way to check.

This is the kind of code we're using for writing to SQL Server:

%macro load2sql(in_tablename, out_table);
	proc sql ;
		connect to ODBC (dsn=&sql_dsn_write user=&sql_user password=&sql_pwd);
		execute (truncate table &out_table) by ODBC ;
		disconnect from ODBC;
		insert into sql_writ.&out_table select * from &in_tablename;
	quit;
%mend load2sql;

What would you propose as the next step? To change my local SAS installation to use UTF-8, and run the program locally to see if that makes a difference?

[Ksharp]

1)First choice, make DB and SAS have the same encoding like 'utf-8' .
2)Second, try other ODBC driver other than "SQL Server Native Client 11.0"

[EinarRoed]

Could you please recommend a good way to force the SAS session to run using 'UTF-8' encoding? I've tried changing the encoding so many ways (now and earlier, when I tried to make it use 'WLATIN1') but it always sticks with LATIN9 (based on the output of proc options option=encoding). Seems to be a plethora of ways to do it, none of which seem particularly intuitive for a beginner. Not sure what's the most efficient way when I just want to test it for 1 program.

This seemed like a decent approach. But I have no such thing as a SASFoundation folder, nor any files named exactly "sasv9.cfg". Despite running Windows and SAS 9.4.

In regards to point 2, I've tried using all the SQL Server driver options for the ODBC now. Unfortunately they didn't work.

[Tom]

How are you currently running SAS?  I suspect that you are using some interface like Enterprise Guide or SAS/Studio to run SAS because the version number you listed for SAS does not make any sense as a SAS version.

You need to check with your local SAS support team and have them setup a way for you to run SAS with the encoding you need.  If you are running SAS on your PC then you should have an Start Menu item to run SAS with Unicode support.  If you are running SAS from the command line on a remote computer you should have both a regular sas command and perhaps an additional command like sas_u8 to run with UTF-9 encoding.  If you are using some interface then you should have multiple SAS services configured that you can connect to that are running different encodings.  For example in SAS/Studio it might look like this:

[EinarRoed]

Thanks Tom. My general SAS installation is 9.4 M5 x64. I'm running the SAS-program via Enterprise Guide 7.15 HF.

My SAS software:

• SAS Universal Reader
• SAS Enterprise Guide
• SAS Data Integration Studio

The local support team has very limited experience with SAS, and I lack access to make changes to the SAS installation (I can only use our default package from Windows Software Center). Very little flexibility in how to solve this. Might have to consider bringing in an external SAS guy to help with this problem.

[ChrisNZ]

Encoding is just an entry (one line) in the configuration file used when calling SAS.

You can see what configuration file(s) are used on your server session by running proc options.

[Ksharp]

if you are under SAS server, talk to SAS admin , I think should add a environment variable (like: export encoding=....) in " sas_env_local " file to change encoding .
And better talk to sas support how to change encoding .