Appending Table Contents

[jwhite]

Hi all,

I've got a 'proc contents' statement inside a do loop which currently outputs the contents (library name, member name, variable name, etc) into a table for each table in a given folder.

I'd like to have my sas code all append these contents into one table, so I have an 'all contents data' table if you will.

Any suggestions?

Thanks!

[ChrisBrooks]

You can do this without a loop - just assign a library to the folder and run something like this

proc contents data=sashelp._all_ out=alltables;
run;

[ballardw]

Or

proc sql;
   create table want as
   select *
   from dictionary.columns
   where libname='SASHELP'
   ;
quit;

or

data want;
   set sashelp.vcolumn;
   where libname='WORK';
run;

In these two the data source stores the libname in uppercase so is bit more finicky than proc contents.

If you have lots of libraries and data sets then the Proc SQL version tends to run a bit quicker than the data step.

[jwhite]

@ChrisBrooks and @ballardw, thank you for the suggestions!

One other question, and a little context...

We're attempting to comb through a folder and isolate the sas dataset objects (those with extension 'SAS7BDAT'), then spit out a graphic showing relationships between the tables. This is why, I've got this within a DO loop as I'm looping through my subset of objects that are sas datasets.

Basically, I'm attempting to do what was done in the following SAS Global paper using sas code and an open source graphic package called Graphicviz.

http://support.sas.com/resources/papers/proceedings13/267-2013.pdf

My follow-up question is...in that document, they are referencing 'rmap'. No one on our team is familiar with this, but I assume it is a table with contents of all the tables and it is that table that is used as a reference to make the connection. Does that look right? So, using Chris Brook's example, I would think that would mean the contents table we created called 'Alltables'. Or am I misunderstanding what 'rmap' is?

Thanks again. Great community!

-Jeff

[ballardw]

You might find this bit of code handy:

Proc tabulate data=sashelp.vcolumn;
   where libname='LIB';
   class memname name type;
   table
         name*type,
         memname*n=''
         /misstext=' '
   ;
run;

This creates a table of all the variables in the named library as row header, the type (num or char in case the same name has both in different sets) and columns with the dataset names. A 1 indicates presence (or I typically use a custom format to show an X.

RMAP in that paper is a data set. All the details they provide is:

Previously generated relationships among the datasets are stored in Rmap dataset.

I didn't find a good example of how that was created in a quick scan.

[jwhite]

Yeah, it's hard to tell what rmap actually is.

On the first page of the document, last paragraph of the Step-by-Step section, it says:

Lastly, macro, by using the power of GraphicViz, produces a relationship diagram where the relationships among

datasets are shown graphically. If any dataset has a variable that has the same name and type that another dataset

in the library also has, macro will draw that out on the report.

This sounds like the macro will make the connection on its own.

What led me to this paper was the idea that just the macro and an open source graphic tool could find, and graph, the relationships for us. Save us time where we have hundreds of tables.

If we need to already chart out the relationship...I'm not sure what savings I'm getting here.

But maybe I totally misunderstood the goal of the macro in the paper...

[ballardw]

---

@jwhite wrote:

Yeah, it's hard to tell what rmap actually is.

On the first page of the document, last paragraph of the Step-by-Step section, it says:

Lastly, macro, by using the power of GraphicViz, produces a relationship diagram where the relationships among

datasets are shown graphically. If any dataset has a variable that has the same name and type that another dataset

in the library also has, macro will draw that out on the report.

This sounds like the macro will make the connection on its own.

What led me to this paper was the idea that just the macro and an open source graphic tool could find, and graph, the relationships for us. Save us time where we have hundreds of tables.

If we need to already chart out the relationship...I'm not sure what savings I'm getting here.

But maybe I totally misunderstood the goal of the macro in the paper...

---

I didn't consider what was presented as an actual macro as it didn't use a %macro/ %mend definition and so thought the code was incomplete.

If you have SAS Graph licensed you should have access to SAS Graph NV Workshop. This provides an interface and a set of tools for drawing a number of networking type diagrams, connections if you will, between nodes that basically have a from and to variable with identification information. So it could create a connection from a variable name to all the data sets with that variable (name and memname)

I'm not sure what kind of information you were hoping to glean from the graphic(s) as I would expect that not many data sets and variables would generate a pretty hard to follow document.

[jwhite]

Really we'd just like to be able to chart out the relationships between the sas datasets in our warehouse. We'd rather not sort through 400 tables, find the applicable ones (there is some legacy junk in there), and draw connections between them. I was hoping this paper would help out and that the macro could look at the contents of each sas dataset and, for example, match variables for each fact table to dim tables, and using that graphic tool, render some star schemas.

Are you aware of any report that can run that'll look at maybe the metadata and make connections? Anything in Management Console or DI Studio, perhaps?

I don't think we have SAS Graph NV Workshop, but will check.

[ChrisBrooks]

You might want to try contacting the authors of the paper (their email addresses are at the end of the paper). It's a five year old paper so they may have changed or no longer be around but if they are then I would expect they would be happy to provide any further details they can. I know from personal experience that paper authors are usually delighted to find their work is useful to other people. 

[ballardw]

---

@jwhite wrote:

Really we'd just like to be able to chart out the relationships between the sas datasets in our warehouse. We'd rather not sort through 400 tables, find the applicable ones (there is some legacy junk in there), and draw connections between them. I was hoping this paper would help out and that the macro could look at the contents of each sas dataset and, for example, match variables for each fact table to dim tables, and using that graphic tool, render some star schemas.

Are you aware of any report that can run that'll look at maybe the metadata and make connections? Anything in Management Console or DI Studio, perhaps?

I don't think we have SAS Graph NV Workshop, but will check.

---

You might want to contact SAS tech support or sales. I know there are more SAS modules out then I am actually familiar with and I have never had access to any of the DI or server management tools.

[jwhite]

@ballardw, @ChrisBrooks, more good suggestions, thanks!

I've reached out to SAS Help to see if there are any sort of reports or modules to assist with this.

Another option came to mind...

MS SQL might have an output that we can make relationship connections with.

Using the first part of the code in the 'macro' of this paper, I've been able to produce a work.dataset with all of the sas datasets in a folder. Being a SAS programming novice, any tips on how to use SAS code to then output/migrate this list of tables to MS SQL Server? I have already made a MS SQL Server connection with DI Studio, so can push out a sas dataset using 'Table Loader' and pointing the table to a library with a MS SQL Server connection.

However, if I'm doing multiple tables in bulk, and not one at a time, I would think I could use the contents of this work.dataset in a User Written Code node in DI Studio and a Datastep or Proc Migrate and _copy_ all the tables to the MS SQL Server library.

Am I right, and this is possible?

Thanks again for all of your help!

-Jeff