topic Database Encoding in SAS Data Management

Database Encoding

Jchapman — Tue, 08 Jul 2014 19:56:16 GMT

When I use proc append it creates table in UTF-8? In config I have encoding as wlatin1. What is causing proc append to create tables using this encoding? Its a major problem because I then get ERROR: File <filename> cannot be updated because its encoding does not match the session encoding or the file when I append from work (which is in wlatin1).

James

Re: Database Encoding

ballardw — Tue, 08 Jul 2014 21:03:24 GMT

Is the BASE data set encoded as UTF-8 BEFORE the append? If you received the data set from someone else that is a likely culprit.

Re: Database Encoding

Jchapman — Tue, 08 Jul 2014 21:27:23 GMT

The base data set is WLatin. Did confirm this encoding. Dataset was pulled using explicit sql pass through from sql server.

Re: Database Encoding

TomKari — Tue, 08 Jul 2014 21:33:27 GMT

If you run

PROC DATASETS;

CONTENTS DATA=dsn;

RUN;

on all of the datasets participating in your append, it will display the encoding of each one.

Also, you can use PROC OPTIONS OPTION=ENCODING to see what your default is.

Re: Database Encoding

Reeza — Tue, 08 Jul 2014 21:40:12 GMT

SAS does appear to use the encoding of the base data set in an append proc, verify your types before the proc append via proc contents and after.

proc contents data=sashelp.class;

run;

data class (encoding=wlatin1);

set sashelp.class;

run;

proc contents data=class;

run;

proc append base=class data=sashelp.class;

run;

proc contents data=class;

run;

Re: Database Encoding

TomKari — Tue, 08 Jul 2014 21:44:19 GMT

Not sure how to figure out the encoding of a SAS/Access connection...maybe pull it into a WORK dataset, and check that?

Tom

Re: Database Encoding

Jchapman — Tue, 08 Jul 2014 21:59:12 GMT

Oddly when I open SAS proc options encoding says encoding is WLatin1. But after I run program it says encoding is UTF-8. Very very odd seeing as I didn't think you could change option encoding except at SAS execution? Also, no encoding is specified in program.

Re: Database Encoding

Reeza — Tue, 08 Jul 2014 22:19:50 GMT

Are you using rsubmit? Running on a server and local?

Re: Database Encoding

jakarman — Wed, 09 Jul 2014 08:46:00 GMT

Something to know:
- Every SAS session has a run time encoding, this one is seen with proc options.
- Every SAS dataset has his own encoding setting this one is a SAS dataset setting (since 9 this has changed)

- Every DBMS has his own encoding with SAS/Access the translation settings are to be configures.
- Eguide is standard having an UTF8 encoding. This is also common with MS-office html (web) etc.

For fun most people are not aware of differences with those encodings still working like the 1980's in single-byte (latin) approach.
- SAS sessions supporting utf8 must need to be started with a dedicated start-scritp. They are difficult to combine with latin1 types although this is possible.

- Utf8 is for a the part of latin-1 for the most chars single-byte exchangeable.

Proc append is not creating a dataset but is combining two different ones.

Questions:
- are the BASE and update dataset for proc append both SAS datasets?

- When they are both SAS datasets do they have the same encoding.
- are you appending to an exterenal DBMS?

- What is the encoding of the DBMS and the settings in the dbms client connected to SAS/Access
The dbms client is the one that will give the encoding to SAS/Access, the RDBMS may have an other encoding.e

Rather sumbersome these topics. For the programming part there is a NLS guide: SAS(R) 9.4 National Language Support (NLS): Reference Guide, Second Edition