SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Merge External Data Dictionary Information into DI Studio

Reply
Contributor
Posts: 20

Merge External Data Dictionary Information into DI Studio

Someone on my team saw a demonstration of DI Studio where the notes field for each data item had been populated from a SQL Server/ORACLE data dictionary.

This would be of great value to us as we access a great deal of SQL Server data in DI.

Respected Advisor
Posts: 3,896

Re: Merge External Data Dictionary Information into DI Studio

Every single dot version of DIS adds new capability. It is therefore really important that you always state which version you're talking about.

Not sure what you mean by "notes field for each data item". Are you talking about variable labels here (column descriptions)? If so then simply create a libname pointing to the data base and register the tables in metadata. This should create SAS table metadata including the labels as derived from the DB tables.

Contributor
Posts: 20

Re: Merge External Data Dictionary Information into DI Studio

We'll be moving to DIS 4.3.  By the notes section

Notes_in_DIS.jpg

The SQL Server tables we're referencing are already registered.  The descriptions we get in the label field are just

repeats of the column names.

But most of the tables have real data dictionaries with useful information. It's this information we want to merge in.

Super User
Posts: 5,257

Re: Merge External Data Dictionary Information into DI Studio

There is a LIBANME option DBSASLABEL that lets you specify the COMPAT. That will give you the database column names as labels (like you already got). My guess is that SAS in future releases will have support (on an engine level) will support transmission of database description fields into SAS labels.

My have solved this by writing a macro that will import this information into SAS metadata (along with information such as constraints).

Data never sleeps
Contributor
Posts: 20

Re: Merge External Data Dictionary Information into DI Studio


Thanks.  Is there a reference for this macro?

Sounds as if it may help.

Respected Advisor
Posts: 3,896

Re: Merge External Data Dictionary Information into DI Studio

I would have opted for DBSASLABEL as solution for what you want. But then checking the documentation for SAS/Access to SQL Server it appears that for some reason this option is not available.

http://support.sas.com/documentation/cdl/en/acreldb/63144/HTML/default/viewer.htm#n0gz66qe8msnkyn1jg...

I have no doubt that Linus wrote a very robust macro to update SAS metadata, BUT: be very careful when writing to SAS Metadata! It's not recommended and if you get it wrong then you can corrupt your whole repository.

If the number of tables is manageable then one way to go could be to run some queries writing the wanted information to textfiles. You can copy/paste whole lists (strings separated by CRLF) into table metadata opened with DIS or SMC (eg. the variable labels) very similar of how you would do it in an Excel spreadsheet.

Never used Notes metadata though and so far didn't see lot of value in it. But I might learn something new here.

Ask a Question
Discussion stats
  • 5 replies
  • 327 views
  • 1 like
  • 3 in conversation