SAS change data type when sending it by ODBC

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?

Solution

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 gepe=nerate DBTYPE= option value 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.