PROC CONTENTS for Database Tables ONLY

[Doc_Duke]

Hi,

I wanted to do a PROC CONTENTS and get the output for just the tables in a database referenced by a LIBNAME. When I run

PROC CONTENTS DATA=mylib._ALL_;

it show all the database views and queries as well (they are tagged as such in the "DBMS Member Type" column.). CONTENTS lists their "SAS" Member Type as DATA, so the MEMTYPE= option doesn't cut it.

Any other ideas of how to limit the output?

Thanks,
Doc Muhlbaier
Duke

[Ksharp]

Hi.
How about use dictionary.tables which also contain the information you want.
[pre]
proc sql;
select *
from dictionary.tables;
quit;
[/pre]


Ksharp

[buckeye]

Following Ksharp's tip, it appears this should work:


LIBNAME DAT 'my:\mylib';

PROC SQL ;
SELECT DISTINCT 'PROC CONTENTS DATA =DAT.' || COMPRESS(MEMNAME,' ') || ';'
INTO :STR SEPARATED BY ' '
FROM DICTIONARY.COLUMNS WHERE MEMTYPE='DATA' ;
QUIT;

&STR;

RUN;

You may want to change the where clause as this:

WHERE MEMTYPE='DATA' AND MEMNAME LIKE 'mytbl%'

to list all tables with name begining 'mytbl'.

Hope this helps.

[Doc_Duke]

I figured it was in the DICTIONARY somewhere, though it took multiple tables to get it. I ultimately made it into a macro. I'm sure it could be further spiffed with error checking and such, but this will suit my purposes.

Doc

%MACRO DBMScontents(dbmslib,outtab=Contents);
* Print the contents of a DBMSs tables only;

* only keep column attributes that are informative via DBMS;
PROC SQL;
CREATE TABLE &outtab AS
SELECT col.memname,
col.varnum,
col.name,
col.type,
col.length,
col.format
FROM
dictionary.columns AS Col,
dictionary.tables AS table
WHERE table.dbms_memtype='TABLE'
AND table.libname =UPCASE("&dbmslib")
AND table.libname=col.libname
AND table.memname=col.memname
ORDER BY col.memname, col.name
;
QUIT;
RUN;

PROC PRINT DATA=&outtab NOOBS;
TITLE "Contents of Tables in LIBNAME &dbmslib";
BY memname;
PAGEBY memname;
RUN;

%MEND dbmscontents;