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

[Filipvdr]

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? 

[ChrisNZ]

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;

[Filipvdr]

Correct.

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

[andreas_lds]

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;

[Filipvdr]

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? 

[andreas_lds]

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.

[Filipvdr]

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.