topic Re: Setting correct format when metadata updates using sas access to MS Server in Administration and Deployment

Setting correct format when metadata updates using sas access to MS Server

John_Wick — Wed, 21 Dec 2022 16:38:18 GMT

Hello everyone!

We have a table in the database with Cyrillic characters.

To work correctly, the format of this field must be 2 times longer than the length of the field in the database. When we use SAS Access to oracle, sas sets the correct format 2-4 times larger. But when we use SAS Access to ms server, the format is equal to the length of the field.

How can I fix this so that when updating metadata, the format of the field becomes 2-4 times the length of the field in the database?

Re: Setting correct format when metadata updates using sas access to MS Server

SASKiwi — Wed, 21 Dec 2022 23:30:08 GMT

Please post the current code you are using to update both Oracle and SQL server. Also please provide details of the column definitions in the databases. Is it varchar by any chance? Also can you check your SAS encoding setting by running this:

proc options option=encoding;
run;

Re: Setting correct format when metadata updates using sas access to MS Server

John_Wick — Thu, 22 Dec 2022 11:06:48 GMT

Metadata is updated via the SAS Management Console.
The type of field in databases is varchar.
Tiers:
1) Oracle encoding is utf8, SAS session is cp1251.
2) MS Server encoding is utf-8, SAS session is utf-8.

Re: Setting correct format when metadata updates using sas access to MS Server

Quentin — Thu, 22 Dec 2022 12:25:11 GMT

@John_Wick wrote:
Metadata is updated via the SAS Management Console.
The type of field in databases is varchar.
Tiers:
1) Oracle encoding is utf8, SAS session is cp1251.
2) MS Server encoding is utf-8, SAS session is utf-8.

As I understand it, cp1251 is a single byte character set. and UTF-8 is multi-byte. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/nlsref/n0ck4l40qlw2pmn1lbuj2zo1izxi.htm

I think the difference in SAS session encodings is probably causing the difference. In your cp1251 SAS session that talks to Oracle, SAS knows it is going from a single byte character set to multi-byte, so it expands the lengths and formats of variables. In your utf-8 SAS session that talks to MS SQL Server, since both sessions are UTF-8 SAS doesn't need to modify the lengths of variables. You could try testing this by using SAS Access to SQL Server from the SAS session with cp1251 encoding, and see if it then does the variable length expansion you are expecting.

Re: Setting correct format when metadata updates using sas access to MS Server

SASKiwi — Thu, 22 Dec 2022 22:32:51 GMT

Depending on how you are updating the database the DBTYPE option might be useful in overriding the default database column lengths although obviously that will only work for new tables.