Problem: When I send table using this code:

libname example odbc datasrc=test insertbuff = 10000;
 

data przyklad;
    small='zażółć gęślą jaźń';
    big=upcase(male);
    format today yymmddd10. time time. now E8601DT19.;
    today=date();
    time=time();
    now=datetime();
run;
 
data sql_serw.tmp_polskie_12;
    set przyklad;
run;

So this code create sample table with some polish characters in data and the problem is when i send it to DB from SAS enviroment polish characters show as garbage chars. I think the point is in the data type becouse it should be nvarchar but on the DB is varchar. Here is the output in DB in Microsoft SQL Server Management Studio

If I use code(its not good becouse there is a lot of tables and columns i have to send):

data example.table(dbtype = (col1='nvarchar(60)' col2='nvarchar(60)'));
    set sasLib.tableInSas;
run;

Polish characters shows up in sql corectly.

The problem is in most of the data bases postgree,mysql,mssql.

Have you any ideas where is the problem?

Problem: When I send table using this code:

libname example odbc datasrc=test insertbuff = 10000;
 

data przyklad;
    small='zażółć gęślą jaźń';
    big=upcase(male);
    format today yymmddd10. time time. now E8601DT19.;
    today=date();
    time=time();
    now=datetime();
run;
 
data sql_serw.tmp_polskie_12;
    set przyklad;
run;

So this code create sample table with some polish characters in data and the problem is when i send it to DB from SAS enviroment polish characters show as garbage chars. I think the point is in the data type becouse it should be nvarchar but on the DB is varchar. Here is the output in DB in Microsoft SQL Server Management Studio

If I use code(its not good becouse there is a lot of tables and columns i have to send):

data example.table(dbtype = (col1='nvarchar(60)' col2='nvarchar(60)'));
    set sasLib.tableInSas;
run;

Polish characters shows up in sql corectly.

The problem is in most of the data bases postgree,mysql,mssql.

Have you any ideas where is the problem?

• sas
• odbc

• Is the goal to move data into existing database tables? Or to also create the tables? Do you have the same trouble with the encoding of the character fields if you just use PROC APPEND to add observations into an existing database table? – Tom Commented Feb 10 at 14:53
• The goal is to create new table in the DB, I create new tables. I can try append some records to existing table and I ll try to append records to an existing table with good endcoding. – MSiedlecki Commented Feb 10 at 15:02
• I just try appending and it works fine, appendet data to table with good column types nvarchar is working. I got some warning messages in log but data is fine. WARNING: Variable male has different lengths on BASE and DATA files (BASE 180 DATA 26). WARNING: Variable wielkie has different lengths on BASE and DATA files (BASE 180 DATA 26). – MSiedlecki Commented Feb 10 at 15:30

1 Answer 1

I don't think SAS has an option to change its default behavior when creating a new dataset (table) in a foreign database. You might want to check the settings for your database and/or the ODBC driver you are using to connect to it.

But you could use some code generation on the SAS side to make it easier to specify the DBTYPE option.

First get a list of the variables in the SAS dataset you are copying from. You can use PROC CONTENTS or SQL query of DICTIONARY.COLUMNS to get the list. (Or use this macro %contents that creates a dataset that combines the types of information provided by both.)

For a small list of variables you could put the generated DBTYPE= option values into a macro variable:

proc sql noprint;
 select catx('=',name,quote(cats('nvarchar(',length',')')))
   into :dbtype separated by ' '
   from contents
   where type='char'
 ;
quit;

Which you could then use in your data step:

data example.table(dbtype = (&dbtype) );
  set sasLib.tableInSas;
run;

Experiment to determine what length you need to use. Perhaps the storage length of the SAS variable (number of bytes) is too large for the NVARCHAR() variable in your remote database since it probably counts number of characters instead. But if your current SAS session is using a single byte encoding then perhaps you need a larger length in the remote database if it is using a multi-byte encoding.