topic Re: A question on SAS dictionary tables in SAS Procedures

A question on SAS dictionary tables

Vickey — Wed, 15 May 2013 18:53:50 GMT

I am new to use SAS dictionary tables. I have ODBC connection to an oracle database. I do not have a database diagram and try to use SAS dictionary to display the tables in this database. I can successfully display the members (the library I defined) of my dictionary. I wonder if I can display the data/tables within the library and how.

Here are the codes I wrote.

/*my connection to the database*/

libname sql odbc prompt; /*define sql as the libname*/

%put %superq(sysdbmsg);

/*display all the libraries i have ever defined*/

proc sql;

select *

from dictionary.tables;

quit;

Library sql is one of them. The member name is CTAR_Datamart_prod and name is ODBC. all other information are empty. How can I display the tables in this sql library. Thanks.

Re: A question on SAS dictionary tables

Haikuo — Wed, 15 May 2013 19:18:28 GMT

For Oracle tables, check out this paper:

http://www.scsug.org/SCSUGProceedings/2011/poling2/FINDING%20ORACLE%20TABLE%20METADATA%20WITH%20SAS.pdf

For Other database, you can find their Meta data info here:

Information schema - Wikipedia, the free encyclopedia

Haikuo

Re: A question on SAS dictionary tables

sascom10 — Wed, 15 May 2013 19:34:58 GMT

Also try exploring proc datasets, I guess that shiould help you!

Re: A question on SAS dictionary tables

Ksharp — Thu, 16 May 2013 02:51:52 GMT

Check this out.

http://support.sas.com/kb/38/112.html

Ksharp

Re: A question on SAS dictionary tables

Patrick — Thu, 16 May 2013 11:13:26 GMT

When using Oracle I find it very helpful to also use SQLDeveloper (free client, can be downloaded from Oracle).

...and as a hint: When downloading/installing the 64bit Windows SQLDeveloper version point it to a 32 bit jdk (version 6.x) - took me a lot of Googling to figure this one out.

Re: A question on SAS dictionary tables

Haikuo — Thu, 16 May 2013 13:36:17 GMT

Thanks, KeShan. This is indeed very handy.