SASBI.net

There are two things that annoy me when coping data from other DBs into SAS. One can copy neither indexes nor field’s labels/comments. I don’t know what to do with indexes but there is a workaround for labels/comments.

The problem occurred after creation of the decision tree schema for presentation. There were no labels in the table copied from Oracle. Thus schema contained odd variable names instead of clear descriptions. But there were comments in Oracle but they were not copied with the data.

We decided to copy comments into a separate table and use a data step to set labels in the table with data.

Oracle (like SAS) has system tables that contain information about all Oracle tables. For instance columns comments are available in the SYS.ALL_COL_COMMENTS table. Knowing the schema name (similar to SAS library) and the table name one can get comments using pass-through facility:

proc sql;
  connect to odbc as Oracle(datasrc="Oracle" user="***" password="***");

  create table work.labels as
  select *
  from connection to Oracle(
    select *
    from sys.all_col_comments t
    where t.owner = '<имя схемы>'
    and t.table_name = '<имя таблицы>'
  );

  disconnect from Oracle;
quit;

The WORK.LABELS table contains the following columns:

• OWNER – owner (creator) of the Oracle schema,
• TABLE_NAME,
• COLUMN_NAME,
• COMMENTS.

One can use label statement in a data step to set up comments:

label <column name>=’<comments>’

We decided to automate this process with a data step that reads WORK.LABELS table and creates c:\update_labels.sas file containing all the label statements.

data _null_;
  file "c:\update_labels.sas";
  set <name of table with data> end = last;
  COMMENTS = compress(COMMENTS,"'");

  if _N_ = 1 then do;
    put "data work.data_with_labels;";
	put "  set _temp;";
  end;

  put " label "column_name" = '"COMMENTS"';";

  if last then put "run;";
run;

Next we run the code:

%INCLUDE "c:\update_labels.sas";

And delete the file:

options noxwait;
x 'del c:\update_labels.sas';

As a results WORK.DATA_WITH_LABELS table has labels that we downloaded from Oracle.