topic Re: How to read scandinavian character (äöå) to SAS from Oracle database in SAS Programming

How to read scandinavian character (äöå) to SAS from Oracle database

pietinma — Mon, 18 Oct 2021 13:08:57 GMT

Hi,

I want to do sql query to Oracle database from a SAS session. While we are in Finland there are Scandinavian characters (ä,ö,å) in the database. When I do sql query in SAS I get ä as a, ö as o and å as ?. What should I do that scandinavian characters displayed right in SAS?

This is encoding in my SAS session.

proc options option=encoding; run;

ENCODING=WLATIN1 Specifies the default character-set encoding for the SAS session.

In SAS Oracle sql query result

ä is a

ö is o

å is ?

This is encoding in Oracle.

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

NLS_CHARACTERSET WE8ISO8859P15

In Oracle query

ä is ä

ö is ö

å is å

Regards

Markku Pietinen

Re: How to read scandinavian character (äöå) to SAS from Oracle database

ChrisHemedinger — Mon, 18 Oct 2021 14:26:54 GMT

Whenever possible, I recommend using ENCODING=UTF8 in SAS. It's a startup option so it must be added to the SAS start command or to the config file. SAS provides a config file for this in ../nls/u8 in the SAS application folder. Example on Windows:

-config "C:\Program Files\SASHome\SASFoundation\9.4\nls\u8\sasv9.cfg"

Re: How to read scandinavian character (äöå) to SAS from Oracle database

pietinma — Thu, 21 Oct 2021 10:17:09 GMT

Thank you for your reply,

This libname statement solved the problem:

libname o oracle user=&user pw=&pw path=k2000te dbprompt=no encoding ='wlatin1';

Why does SAS not read it from the configuration file

proc options option=config;

run;

CONFIG=( "C:\PROGRA~1\SASHome\SASFOU~1\9.4\sasv9.cfg" "C:\Program Files\SASHome\SASFoundation\9.4\nls\en\sasv9.cfg" )

Specifies the configuration file that is used when initializing or overriding the values of SAS system options.

.....

-SET SASCFG "C:\Program Files\SASHome\SASFoundation\9.4\nls\en"

-LOCALE fi_FI

-ENCODING wlatin1

-TEXTURELOC !SASROOT\common\textures

...

Re: How to read scandinavian character (äöå) to SAS from Oracle database

ChrisHemedinger — Thu, 21 Oct 2021 13:15:35 GMT

Glad you got it working. The SAS session encoding might not pass through to the database automatically depending on how your Oracle client is configured or the settings on the database itself. Good to have that encoding= hook for the libname statement.