CODEWITHSUNDEEP
sqlformatsas
Lovnlust
Lovnlust

Reputation: 1537

Modify the dataset format in SAS on a `sql-base` server

Usually I would use proc datasets lib= ; modify to change the format/informats of the columns in a datasets. But when I apply this syntax to a dataset on a sql-based server. The error shows

ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ODBC engine.

I found some articles that may explain the problem. Here's one. http://support.sas.com/kb/37/015.html

Moreover, whatever dataset I created in the network, the format/informat/length is changed to its 'default' setting. E.g date9. -> datetime 22.3.

But still I don't understand why it happens. Is there something pre-defined in the network and the architecture of the server is not 100% compatible with SAS?

Upvotes: 0

Views: 751

Answers (1)

K.I.
K.I.

Reputation: 807

When you modify format, You alter table.

Suppose you have some diferent database (SQL) servers. Example Oracle, MS SQL, MY SQL. All of them have their own dialect on altering table. When You write modify column; ... you are altering table. But SAS does not which dialect to try. That is why alter table procedure is not supported from datasets procedure.

You can update that table using database server dialect, but it needs to be added from proc sql procedure. Like it was writen in that article

 execute( alter table table-name ...specific-Oracle-syntax...)by oracle;

data9. I think You mean date9.. Well it is SAS format. Other database servers, can have or can have not this format. By default they will create database default formats.

Upvotes: 1

Related Questions