topic Re: PROC CONTENTS for Database Tables ONLY in SAS Programming

PROC CONTENTS for Database Tables ONLY

Doc_Duke — Wed, 20 Apr 2011 21:16:18 GMT

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

Re: PROC CONTENTS for Database Tables ONLY

Ksharp — Thu, 21 Apr 2011 06:11:18 GMT

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

Ksharp

Re: PROC CONTENTS for Database Tables ONLY

buckeye — Thu, 21 Apr 2011 12:12:29 GMT

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.

Re: PROC CONTENTS for Database Tables ONLY

Doc_Duke — Thu, 21 Apr 2011 15:40:20 GMT

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;