Macro program to pull out library details from the server

Rabindra — Mon, 09 Feb 2015 07:13:41 GMT

I am looking to develop a macro so that that macro can pull out the list of libraries details from a server along with the memtype information.

For example I am working on SASApp Server and the server has 50 libraries, in these libraries each library contains 50 to 60 tables so I need to pull out all the information about the libraries and tables along with the libref, path and schema details.

Eventually we can use Proc contents data=libref._all_ out=dataset name; run;

The problem here is every time I have to give the libref name to pull out the information about a paticular library. I need the program should itself assign the libref and populate information about the list of libraries and memtype details.

Re: Macro program to pull out library details from the server

PoornimaRavishankar — Wed, 11 Feb 2015 20:09:24 GMT

Is this UNIX or Windows or anything else? It looks like you will need to either embed your SAS code within the native environment's programming language or the other way around. In UNIX, this can be achieved using shell scripting and some SAS; for other environments, we will have to check. Either way, some more details might help.

Re: Macro program to pull out library details from the server

Astounding — Wed, 11 Feb 2015 20:29:05 GMT

Are you able to get an automated list of the folders you would like to report on? If so, the rest is nearly easy. If not, how would SAS know which folders to examine? You wouldn't want to examine every folder on the system. You wouldn't even want to use the metadata from SASHELP unless you are willing to hardcode the exceptions like WORK, MAPS, etc.

Re: Macro program to pull out library details from the server

ballardw — Wed, 11 Feb 2015 20:36:45 GMT

If you see the information you need then you could use the Dictionary tables with proc sql.

From the online help:

DICTIONARY Tables and Associated SASHELP Views

DICTIONARY Table	SASHELP View	Description
CATALOGS	VCATALG	Contains information about known SAS catalogs.
CHECK_CONSTRAINTS	VCHKCON	Contains information about known check constraints.
COLUMNS	VCOLUMN	Contains information about columns in all known tables.
CONSTRAINT_COLUMN_USAGE	VCNCOLU	Contains information about columns that are referred to by integrity constraints.
CONSTRAINT_TABLE_USAGE	VCNTABU	Contains information about tables that have integrity constraints defined on them.
DATAITEMS	VDATAIT	Contains information about known information map data items.
DESTINATIONS	VDEST	Contains information about known ODS destinations.
DICTIONARIES	VDCTNRY	Contains information about all DICTIONARY tables.
ENGINES	VENGINE	Contains information about SAS engines.
EXTFILES	VEXTFL	Contains information about known external files.
FILTERS	VFILTER	Contains information about known information map filters.
FORMATS	VFORMAT VCFORMAT	Contains information about currently accessible formats and informats.
FUNCTIONS	VFUNC	Contains information about currently accessible functions.
GOPTIONS	VGOPT VALLOPT	Contains information about currently defined graphics options (SAS/GRAPH software). SASHELP.VALLOPT includes SAS system options as well as graphics options.
INDEXES	VINDEX	Contains information about known indexes.
INFOMAPS	VINFOMP	Contains information about known information maps.
LIBNAMES	VLIBNAM	Contains information about currently defined SAS libraries.
MACROS	VMACRO	Contains information about currently defined macro variables.
MEMBERS	VMEMBER VSACCES VSCATLG VSLIB VSTABLE VSTABVW VSVIEW	Contains information about all objects that are in currently defined SAS libraries. SASHELP.VMEMBER contains information for all member types; the other SASHELP views are specific to particular member types (such as tables or views).
OPTIONS	VOPTION VALLOPT	Contains information about SAS system options. SASHELP.VALLOPT includes graphics options as well as SAS system options.
REFERENTIAL_CONSTRAINTS	VREFCON	Contains information about referential constraints.
REMEMBER	VREMEMB	Contains information about known remembers.
STYLES	VSTYLE	Contains information about known ODS styles.
TABLE_CONSTRAINTS	VTABCON	Contains information about integrity constraints in all known tables.
TABLES	VTABLE	Contains information about known tables.
TITLES	VTITLE	Contains information about currently defined titles and footnotes.
VIEWS	VVIEW	Contains information about known data views.
VIEW_SOURCES	Not available	Contains a list of tables (or other views) referenced by the SQL or DATASTEP view, and a count of the number of references.

Retrieving Information about DICTIONARY Tables and SASHELP Views

To see how each
DICTIONARY table is defined, submit a DESCRIBE TABLE statement. This example
shows the definition of DICTIONARY.TABLES:

proc sql; describe table dictionary.tables;

The results are
written to the SAS log.

Definition of DICTIONARY.TABLES

 NOTE: SQL table DICTIONARY.TABLES was created like:  create table DICTIONARY.TABLES ( libname char(8) label='Library Name', memname char(32) label='Member Name', memtype char(8) label='Member Type', dbms_memtype char(32) label='DBMS Member Type', memlabel char(256) label='Data Set Label', typemem char(8) label='Data Set Type', crdate num format=DATETIME informat=DATETIME label='Date Created', modate num format=DATETIME informat=DATETIME label='Date Modified', nobs num label='Number of Physical Observations', obslen num label='Observation Length', nvar num label='Number of Variables', protect char(3) label='Type of Password Protection', compress char(8) label='Compression Routine', encrypt char(8) label='Encryption', npage num label='Number of Pages', filesize num label='Size of File', pcompress num label='Percent Compression', reuse char(3) label='Reuse Space', bufsize num label='Bufsize', delobs num label='Number of Deleted Observations', nlobs num label='Number of Logical Observations', maxvar num label='Longest variable name', maxlabel num label='Longest label', maxgen num label='Maximum number of generations', gen num label='Generation number', attr char(3) label='Data Set Attributes', indxtype char(9) label='Type of Indexes', datarep char(32) label='Data Representation', sortname char(8) label='Name of Collating Sequence', sorttype char(4) label='Sorting Type', sortchar char(8) label='Charset Sorted By', reqvector char(24) format=$HEX48 informat=$HEX48 label='Requirements Vector', datarepname char(170) label='Data Representation Name', encoding char(256) label='Data Encoding', audit char(8) label='Audit Trail Active?', audit_before char(8) label='Audit Before Image?', audit_admin char(8) label='Audit Admin Image?', audit_error char(8) label='Audit Error Image?', audit_data char(8) label='Audit Data Image?', num_character num label='Number of Character Variables', num_numeric num label='Number of Numeric Variables' );

Similarly, you can
use the DESCRIBE VIEW statement in PROC SQL to determine how a SASHELP view is
defined. Here is an example:

proc sql; describe view sashelp.vtable;

topic Macro program to pull out library details from the server in SAS Programming

Macro program to pull out library details from the server

Re: Macro program to pull out library details from the server

Re: Macro program to pull out library details from the server

Re: Macro program to pull out library details from the server

Retrieving Information about DICTIONARY Tables and SASHELP Views