How to diagram SAS set of tables?

[jerry898969]

I was wondering how to create a data diagram using sas? Is it possible to use SAS through VISIO? I have a 60 table directory that I need to represent to my bosses and I'm not 100% how to do that without having to go to a different database software to do it.

Thank You for any help

[Cynthia_sas]

Hi:
I'm not sure what you mean by "use SAS through VISIO" -- if you mean open a SAS program into VISIO and have VISIO automatically generate your data diagram or DFD for you -- I believe the answer is no. However, new with SAS 9.2 is the SAS Code Analyzer that will analyze your code, as explained here:
http://www2.sas.com/proceedings/forum2008/006-2008.pdf
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a003199745.htm

I'm also not sure what you want to diagram??? If you have a 60 table directory are you producing a DATA dictionary??? Most folks use PROC CONTENTS or the SAS Dictionary Tables for that task. If, for example, you wanted to show all the variables, their labels and their formats and the format information, then you could use a combination of PROC CONTENTS and PROC FORMAT FMTLIB for this task. These papers talk about all of the "SASHELP.Vxxx" (or Dictionary) tables for finding out information on your data.
http://www.wuss.org/proceedings08/08WUSS%20Proceedings/papers/app/app05.pdf
http://www2.sas.com/proceedings/sugi29/237-29.pdf
http://www2.sas.com/proceedings/sugi26/p017-26.pdf
http://www2.sas.com/proceedings/sugi30/070-30.pdf

Once you decide whether you want a data dictionary or a data flow diagram then you'll have some idea of how to proceed.

cynthia

[LinusH]

I suspect that a database diagram is what is asked for.
Visio is not my favorite tool for database diagrams, it's better for flow charts etc (at least my version which is Standard 2003). ERwin and others are better for these kind of drawings.
Either tool does usually use an ODBC connection for importing data base information. I'm not sure how relationships will be deployed in the model. That is probably tool dependent. It might help if you have referential integrity defined on your tables.

Please share your experience if you'll have a go!

Regards,
Linus

[jerry898969]

Cynthia & Linus thank you both for your replies.

A database entity relationship diagram is what am looking to do. Where it shows how all the tables relate to one another. I want it to show table name and all the variables and how they join to other tables. I need a visual representation of my directory of sas data tables.

Thank you again for your help.

Jerry Message was edited by: jerry898969

[Flip]

While many ERD tools can reverse engineer table structures into an ER, SAS does not store foreign keys in the table structure so reslationships are not preserved. Years ago I wrote a set of macros to work with ERwin so some of it can be done. There is just no easy solution.

[jerry898969]

Flip,
Thanks for the reply. Is there any software that will recognize SAS tables and display them then I can do the linking? So how do SAS users represent there data schemas?

Thanks
Jerry

[Flip]

Sorry I don't have any recent information. I used to run an SQL describe to generate the code for a table, then load that into ERWin. I no longer have a copy and the last few shops I have worked at did not have accessible ER tools. So I am just working from memory here.

[Flip]

Do you have SAS ODBC? I would think VISIO should be able to reverse engeneer from that. I don't have it here, so I cannot try it.

You will not get any links between tables unless it uses column names.

[jerry898969]

Flip,
I should have SAS/ACCESS Interface to ODBC Software installed. I'm still new to sas so i'm not sure how to play around with this. What would be the best way to see if I have it installed?

Thanks again so much for your help I do appreciate it.

Jerry

[LinusH]

No, what you need is the ODBC driver for SAS. If you have DMS SAS locally it should work. If you have SAS on a server, you probably need SAS/SHARE and SAS/SHARE*NET software.

Most ERD-tools (including ERwin) can reverse engineer via ODBC.

/Linus

[jerry898969]

Linus,

Within Visio when I try reverse engineering the only option that shows anything about says is when I select "Generic OLE DB Provider". Then it shows me a list of
SAS IOM Data Provider 9.1
SAS Local Data Provider 9.1
SAS OLAP Data Provider 9.1
SAS Share Data Provider 9.1

When I select "SAS Local Data Provider 9.1" it asks for a data source and location and then user name and password. What should the source and location be?

Thanks you

[LinusH]

There are log of documentation and papers describing how to do this.
You can start with the on-line doc:
http://support.sas.com/onlinedoc/913/docMainpage.jsp
Section: SAS ODBC Driver: User's Guide and Programmer's Reference

If you are uncertain about your SAS environment, contact the SAS administrator for your site.

Good luck,
Linus

[jerry898969]

Linus,
Thank you that link has helped. I should have "SAS" as one of my options when i'm trying to create a DSN and I don't so I think that might be the reason why this hasn't worked.

Thank you again and I will post my results.

Jerry

[CaseySmith]

For the local data provider, the Data Source property should be, "A physical directory that contains the SAS data set that you want to access with the connection." (ex. "C:\Program Files\SAS\EnterpriseGuide\4.2\sample\data") Leave the Location empty.

If you want to access SAS data through a server instead of the local file system, use the IOM data provider instead of the local data provider.

See the "SAS Data Providers: ADO/OLE DB Cookbook" section in the OnlineDocs
(http://support.sas.com/onlinedoc/913/docMainpage.jsp) for information on the data providers.