BookmarkSubscribeRSS Feed
mikersas
Obsidian | Level 7

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.

5 REPLIES 5
Patrick
Opal | Level 21

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.

mikersas
Obsidian | Level 7

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.

LinusH
Tourmaline | Level 20

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
mikersas
Obsidian | Level 7


Thanks.  Is there a reference for this macro?

Sounds as if it may help.

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1087 views
  • 1 like
  • 3 in conversation