topic Re: proc dataset in SAS Programming

proc dataset

Steelers_In_DC — Thu, 13 Nov 2014 14:11:30 GMT

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

Re: proc dataset

RW9 — Thu, 13 Nov 2014 14:18:09 GMT

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.

Re: proc dataset

Haikuo — Thu, 13 Nov 2014 14:26:46 GMT

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

Re: proc dataset

Steelers_In_DC — Thu, 13 Nov 2014 14:38:46 GMT

I do not know what you mean by dictionary tables.

Re: proc dataset

RW9 — Thu, 13 Nov 2014 14:45:09 GMT

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;

Re: proc dataset

DeepakSwain — Wed, 18 May 2016 18:53:55 GMT

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