Merge SAS metadata (formats and labels) to SQL Server tables and views

[ScottBass]

I need to add SAS metadata (formats and labels) to SQL Server tables and views such that when downstream users access the table via the meta engine, the table has the formats and labels applied.

Here's some pseudocode:

data mysaslib.demographics; set sashelp.demographics;run;
data mysaslib.demographics2;set sashelp.demographics;format _all_;attrib _all_ label="";run;

proc print data=mysaslib.demographics (obs=10) label;run;
proc print data=mysaslib.demographics2 (obs=10) label;run;

proc append data=mysaslib.demographics base=mySQLlib.demographics;run;

I then define the mysqllib library in SMC, register the SQL Server table, and import metadata.  But of course it doesn't have the SAS metadata.

Is there a way to "merge" the SAS metadata onto the table definition in the SQL Server library?

Also, I have a few SQL Server views against that table, with different columns for each view.  I would like those views to also have the SAS metadata applied.

I'd prefer a programmatic approach, say via PROC METADATA, rather than a process to follow via the SMC.

The end result is I would want:

proc print data=mysqllib.demographics (obs=10) label;run;

to match the output from the 1st proc print above, rather than the 2nd output.

Thanks...

[Kurt_Bremser]

For SAS datasets, I would use proc datasets to get the attributes and restore them on the target:

%let targlib=work;
%let targdata=demo;

data &targlib..&targdata;
set sashelp.demographics (obs=10);
format _all_;
attrib _all_ label="";
run;

title "Dataset stripped of attributes";

proc contents data=&targlib..&targdata;
run;

proc datasets library=sashelp nolist;
contents data=demographics out=work.myattr noprint;
quit;

data _null_;
set work.myattr end=eof;
if _n_ = 1 then call execute("
  proc datasets library=&targlib nolist;
  modify &targdata;
");
call execute('attrib ' !! trim(name));
if format > '' then call execute(" format=" !! trim(format) !! strip(put(formatl,best.)) !! '.' !! strip(put(formatd,best.)));
if label > '' then call execute(" label='" !! trim(label) !! "'");
call execute(";");
if eof then call execute('quit;');
run;

title "Dataset with attributes restored";

proc contents data=&targlib..&targdata;
run;

With views, restoring the underlying datasets' attributes should fix it; if the view changes attributes on its own, the code needs to be changed.

I have no idea if this code would work through a SAS/ACCESS connection.

[LinusH]

Perhaps not a programmatic approach, but still...

Create template tables in SAS with desired metadata, import them into the metadata server.

Then again import columns definition from those template tables to the real SQL Server table definitions.

If your tables share many columns, you take a look at standardizing columns, can also reduce manual work a bit:

https://documentation.sas.com/?docsetId=etlug&docsetTarget=n0kw06lyyymqzin14dl0a1x9dtat.htm&docsetVe...

[ScottBass]

Hi @LinusH,

Yeah that's the approach I've taken.  My code looks something like:

* create objects to assist with metadata creation in SMC ;
* main table ;
* build keep statement based on the table definition ;
proc contents data=pdc.PDC_IDENTIFIED_SCD2 (drop=CHECKSUM) out=contents (keep=name varnum) noprint;
run;
proc sql noprint;
   select name into :keep separated by " " from contents order by varnum;
   drop table contents;
quit;
%put &=keep;

data pdcdata.meta_PDC_IDENTIFIED_SCD2;
   * set column order ;
   if 0 then set pdc.PDC_IDENTIFIED_SCD2;
   * augment with the column attributes ;
   %include attrib;
   * extra columns not in the metadata workbook ;
   attrib
      PDC_IDENTIFIED_SCD2_ID length=8
      VALID_FROM             length=8  format=datetime21.
      VALID_TO               length=8  format=datetime21.
      CURRENT_RECORD         length=$1 
/*    CHECKSUM               length=$32   format=hex32. */
   ;   
   keep &keep;
   call missing(of _all_);
   stop;
run;
proc contents;
run;

PDC is a SQL Server library.  PDCDATA is a SAS library.  %include attrib is a huge (350+ variables) attributes block built from metadata in an Excel workbook (does anyone actually use Excel as a spreadsheet 😉 )

Once these SAS datasets are created:

* Launch SMC

* Navigate to the SQL Server library

* Open the table object

* Delete the rows (I can't delete all rows, as some are referenced by indexes, etc)

* Click Import

* Navigate to the corresponding SAS meta_* table

* Import the columns

* Drag the columns into the desired order

Still, after 10+ years of SAS 9.x, it would have been nice if R&D had enhanced PROC METALIB to do this programmatically.  For example, use PROC CONTENTS against the SAS dataset, then a DATA= option to PROC METALIB which would programmatically merge the SAS metadata from PROC CONTENTS into the SQL Server table metadata.  Surely I'm not the only one that's had to do this over 10+ years?

Also, this approach breaks if you later select "Update Metadata" in SMC.  Plus it's obviously prone to error.

Finally, I'm not the one maintaining the metadata, a colleague is.  And lucky we only need to do this against 4 table objects.

Because this is urgent, I also opened a SAS TS track.  I'll update this post if they suggest a better way.

[LinusH]

PROC metalib might work, I just haven't invested the time to explore it.
Generally I agree with expectation you have on the SW.