topic Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library in SAS Programming

SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

Filipvdr — Mon, 15 Oct 2018 13:26:48 GMT

Hello all,

We have a new SQL library which we will use to store our data.

In Enterprise guide, business users can write datasets to this library. They can pass labels in their query builder but the labels are not stored in SQL, and the table is not stored in metadata this way.

Are there any solutions for this issue?

Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

ChrisNZ — Mon, 15 Oct 2018 22:56:36 GMT

So you are creating tables in SQL server via EG and can't see the table (or variable?) labels?

What happens when you run:

data SQLLIB.TEST(label='aaa'); 
  A=1;
  label A='bbb';
run;

Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

Filipvdr — Tue, 16 Oct 2018 07:49:01 GMT

Correct.

When i run this code the dataset is created with variable a but the label is also "A".

Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

andreas_lds — Tue, 16 Oct 2018 08:17:29 GMT

To store the table in metadata, you need to execute proc metalib:

proc metalib;
   omr (LibUri = "SASLibrary?@libref='YOUR_LIBREF'");

   update_rule=(delete);

   report;
run;

Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

Filipvdr — Tue, 16 Oct 2018 08:29:38 GMT

Yes correct but it will look at the physical table in the SQL server to base his metadata on, and the labels are not hold there?

Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

andreas_lds — Tue, 16 Oct 2018 08:48:04 GMT

Had the same problem some time ago and thought that just by registering the table the problem could be solved. Found some code that

fixes the label in metadata. Unfortunately i can't share the code, it is part of project for a client.

The code uses metadata-functions to get the table-definition using the uri

 searchUri = cats("omsobj:DataTable?DataTable[@Name = 'TABLE_NAME']", 
   "[TablePackage/DatabaseSchema[@Name = 'DATABASE_NAME']");

Next statements load the table-object from metadata and iterate over the columns, the only thing you need is a dataset with column-name and label.

rc = metadata_getnobj(searchUri, 1, tableUri);

numRows = metadata_getnasn(tableUri, "Columns", 1, colUri);

do i = 1 to numRows;
  call missing(colUri, colName);
  rc = metadata_getnasn(tableUri, "Columns", i, colUri);
  rc = metadata_getattr(colUri, "Name", colName);
        
   if h.find(key: colName) = 0 then do;
     rc = metadata_setattr(colUri, "Desc", colLabel);
   end;
end;

Hash-object h has columns colName and colLabel.

I am sure that an easier solution exists.

Re: SAS Enterprise guide - storing labels in a table (which is not in metadata) on a SQL library

Filipvdr — Tue, 16 Oct 2018 12:36:55 GMT

Thanks Andreas for sharing. I was planning to do the same as I don't see any other options then to store every table in metadata and use metadata scripting.