DATA Step, Macro, Functions and more

PROC CONTENTS for Database Tables ONLY

Reply
Trusted Advisor
Posts: 2,116

PROC CONTENTS for Database Tables ONLY

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
Super User
Posts: 10,044

Re: PROC CONTENTS for Database Tables ONLY

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


Ksharp
Contributor
Posts: 24

Re: PROC CONTENTS for Database Tables ONLY

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


LIBNAME DAT 'my:\mylib';

PROC SQL ;
SELECT DISTINCT 'PROC CONTENTS DATA =DAT.' || COMPRESS(MEMNAME,' ') || ';'
INTO Smiley FrustratedTR 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.
Trusted Advisor
Posts: 2,116

Re: PROC CONTENTS for Database Tables ONLY

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;
Ask a Question
Discussion stats
  • 3 replies
  • 2284 views
  • 0 likes
  • 3 in conversation