topic Re: Creating a table containing all fields of all tables inside a library in SAS Programming

Creating a table containing all fields of all tables inside a library

endomorph-21 — Fri, 03 Jun 2022 22:02:45 GMT

Hello. I would like to find a function/statement that extracts all field names of a table. Then I would like to iterate it over all existing tables within a library and list them in a dataset like this

TABLE | FIELD

table 1 | field 1

table 1 | field 2

...

table 1 | field m

...

table 2 | field 1

table 2 | field 2

...

table n | field m

Re: Creating a table containing all fields of all tables inside a library

Tom — Fri, 03 Jun 2022 22:09:00 GMT

Just use PROC CONTENTS.

So if your libref is LIBREF then this code will make a dataset named WANT with one observation per variable per dataset (member).

PROC CONTENTS data=libref._all_ out=WANT; run;

Add NOPRINT option to the PROC statement if you don't need to see the contents printed.

Re: Creating a table containing all fields of all tables inside a library

ballardw — Fri, 03 Jun 2022 23:04:40 GMT

Proc SQL;
    create table want as
    select libname, memname, name
    from dictionary.columns
    where libname='WORK' and memtype='DATA'
    ;
quit;

data otherwant;
   set sashelp.vcolumn;
   where libname='WORK' and memtype='DATA';
run;

SAS keeps all that metadata in special data views in the SASHELP library. You can look at them with any tool you like. The Proc SQL treats them as members of a special set of members in the DICTIONARY (note that is not really a library as more than 8 characters) that has different members that contain information about data variables (columns), data sets, libraries, external files, macros, title statements and more.