proc dataset

[Steelers_In_DC]

I would like to pull proc dataset information from all of the datasets that contain a 'facility_id' variable in them.  Is there any way to do a search like that?  I'm looking at a library that has several hundred tables and I only want the tables with that specific variable.

Thank You in advance,

Mark

[RW9]

No probs.  Check out the SAS metadata tables:

proc sql;

     create table WANT as

     select     *

     from       SASHELP.VCOLUMNS  /* Could also use dictionary tables */

     where     MEMNAME in (select distinct MEMNAME from SASHELP.VCOLUMNS where NAME="FACILITY_ID");

quit;

The above will show you the metadata for all tables which have that variable in.

[Haikuo]

,

Unlike libname and memname, the variable names are stored 'as-is' in this view, so on the safe side,

where upcase(name)='FACILITY_ID'

Regards,

Haikuo

[Steelers_In_DC]

I do not know what you mean by dictionary tables.

[RW9 · Accepted solution]

Dictionary tables comes from SQL, they are pretty much interchangeable with SASHELP.VTABLE/VCOLUMNS, and are the metadata for the table.  E.g.

proc sql;

  create table TMP as

  select * from DICTIONARY.TABLES;

quit;

[DeepakSwain]

Hi there,

I think we may avoid use of "DICTIONARY.TABLES" when we are searching for datasets based on a particular variable. Dictionary.tables do not provide information related to variable name which are available in SASHELP. VCOLUMN.

Regards,

Deepak