topic Re: Merge External Data Dictionary Information into DI Studio in SAS Data Management

Merge External Data Dictionary Information into DI Studio

mikersas — Fri, 20 Apr 2012 18:48:12 GMT

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.

Re: Merge External Data Dictionary Information into DI Studio

Patrick — Sat, 21 Apr 2012 02:20:19 GMT

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.

Re: Merge External Data Dictionary Information into DI Studio

mikersas — Mon, 23 Apr 2012 13:25:48 GMT

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

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.

Re: Merge External Data Dictionary Information into DI Studio

LinusH — Mon, 23 Apr 2012 14:37:42 GMT

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).

Re: Merge External Data Dictionary Information into DI Studio

mikersas — Mon, 23 Apr 2012 14:46:31 GMT

Thanks. Is there a reference for this macro?

Sounds as if it may help.

Re: Merge External Data Dictionary Information into DI Studio

Patrick — Tue, 24 Apr 2012 10:41:41 GMT

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#n0gz66qe8msnkyn1jgnhrr0l2k8y.htm

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.