/*----------------------------------------------------------------------------- File: GetLibraryMetadata.sas Author: John Sabel john.sabel@ofm.wa.gov Washington State Education Research & Data Center Creation date: 10/9/12 Version: 1.13 Purpose Creates a data set containing the metadata for the data sets and views. in a library. You can specify the types of metadata you want reported. You can use a regular expression to filter the data sets and views you wish to be reported on. Parameters LibnameIn: Name of library containing data sets DsnOut: The name of the outputted data set. Metadata: (Optional) The type of metadata you want reported on. These metadata are those in SAS's Dictionary.Table. There are two more possible metadata items beyond those in Dictionary.Table: 1) RecordCount - Similar to NOBS. The NOBS item is still available, but it doesn't work when the library (LibnameIn) uses an ODBC connection that connects to a relational database. 2) ColumnNames - The comma-delimited names of all the columns for each data set in LibnameIn. The default is "memname RecordCount crdate modate nvar ColumnNames" RegExFilter: (Optional) A Perl Regular Expression used to filter the data sets in the "LibnameIn" library. The default is /./, which acts as no filter at all, so by default all data sets in a library will be merged. When you do use this regular expression filter consider using the "I" option in order to make the filter case insensitive. RegExFilter_2: (Optional) Exactly the same as RegExFilter above. Sometimes using two regular expressions in conjunction is easier to use compared to trying to stuff all your logic into a single regular expression. For example RegExFilter can use positive conditions, and RegExFilter_2 can use negative conditions. RegExFilter_3: (Optional) Exactly the same as RegExFilter above. RecordCountWhereClause: (Optional) Allows you to put a subsetting condition for calculating "RecordCount". Debug (Optional) When Debug=Y, the intermediate data set "__TableMetadata" is not deleted. Signature %GetLibraryMetadata(LibnameIn=, DsnOut=, Metadata=memname RecordCount crdate modate nvar ColumnNames, RegExFilter_1=/./I, RegExFilter_2=/./I, RegExFilter_3=/./I, RecordCountWhereClause=, Debug=N) Revisions: Date Version Author Comments 2013-02-20 0.91 John Found that the DES comment in the macro header was too long, so the macro would not compile. I shortened the line. 2013-07-29 1.0 John RecordCount(s) and/or ColumNames had been always fetched, whether ultimately needed or not This macro has been updated so that these values are only fetched if one or both are needed as determined in the Metadata parameter. 2013-12-11 1.01 John Added "> 0" to prxmatch clause in SELECT statement so that statement works in SAS 9.4. Macro had been constrained to collecting meta data on 99 data sets and views. Increased this constraint to 999. 2014-02-11 1.1 John Added 2nd regex filter parameter, RegExFilter_2. 2014-02-13 1.11 John Added RecordCountWhereClause parameter. I added this for my own convenience, so I can get record counts of Informatica MDM hub tables excluding those that have been soft deleted. 2014-02-24 1.12 John Added 3rd regex filter parameter, RegExFilter_3. 2014-06-23 1.13 John Removed DES option from macro definition. Renamed "RegExFilter" parameter to "RegExFilter_1". -----------------------------------------------------------------------------*/ %macro GetLibraryMetadata(LibnameIn=, DsnOut=, Metadata=memname RecordCount crdate modate nvar ColumnNames, RegExFilter_1=/./I, RegExFilter_2=/./I, RegExFilter_3=/./I, RecordCountWhereClause=, Debug=N); %put --- Start of %upcase(&sysmacroname) macro; %local NumDataSets; %local RecordCount; %local ColumnNames; %local _MetadataWithCommas; %local err or; %let err=ERR; %let or=OR; %let _MetadataWithCommas = %qsysfunc(tranwrd(%cmpres(&Metadata.),%bquote( ),%bquote(,))); proc sql; CREATE TABLE __TableMetadata AS SELECT Tables.*, . AS RecordCount, "" AS ColumnNames length = 256 FROM Dictionary.Tables WHERE UPCASE(LIBNAME) = "%upcase(&LibnameIn.)" AND prxmatch("&RegExFilter_1.", MEMNAME) > 0 AND prxmatch("&RegExFilter_2.", MEMNAME) > 0 AND prxmatch("&RegExFilter_3.", MEMNAME) > 0 ; %let NumDataSets=&sqlobs.; quit; %if &NumDataSets.= 0 %then %do; %put &err&or: (&sysmacroname.) The library "&LibnameIn." does not contain any data sets that have names that meets the regular expression requirement "RegExFilter=&RegExFilter.".; %goto exit; %end; proc sql noprint; SELECT MEMNAME INTO :__MemNames1-:__MemNames9999 FROM __TableMetadata ; quit; %do I = 1 %to &NumDataSets.; /* Only determine record counts if needed. */ %if %index(&Metadata.,RecordCount) > 0 %then %do; proc sql noprint; title "&&__MemNames&I Record Count"; SELECT COUNT(*) AS RecordCount INTO :RecordCount FROM &LibnameIn..&&__MemNames&I &RecordCountWhereClause. ; quit; proc sql noprint; UPDATE __TableMetadata SET RecordCount = &RecordCount. WHERE MEMNAME = "&&__MemNames&I" ; quit; %end; /* Only determine column names if needed. */ %if %index(&Metadata.,ColumnNames) > 0 %then %do; proc sql noprint; title "&&__MemNames&I Column Names"; SELECT Name INTO :ColumnNames SEPARATED BY ', ' FROM Dictionary.Columns WHERE UPCASE(LIBNAME) = "%upcase(&LibnameIn.)" AND UPCASE(MEMNAME) = "%upcase(&&__MemNames&I)" ORDER BY VARNUM ; quit; proc sql noprint; UPDATE __TableMetadata SET ColumnNames = "&ColumnNames." WHERE MEMNAME = "&&__MemNames&I" ; quit; %end; %end; proc sql; CREATE TABLE &DsnOut. AS SELECT %sysfunc(compress(&_MetadataWithCommas., '"')) FROM __TableMetadata ; quit; %exit: %if &Debug.=N %then %do; proc sql; DROP TABLE __TableMetadata; quit; %end; %put --- End of %upcase(&sysmacroname) macro; %mend GetLibraryMetadata; /* * Test data*******************************************************************; data NameTestData1; input FirstName $ LastName $; cards; Jason Long Audrey Flytrap ; run; data NameTestData2; input FirstName $1-10 LastName $12-21 ShoeSize 24-25; cards; Johnjacob Longfellow 11 Dominique Luvidillawa 7 ; run; data JustNumbers; input NumberOne; cards; 1 2 3 4 5 6 7 ; run; * Example macro calls using test data *****************************************; * Report on the the number of observations for each data set in the WORK library.; %GetLibraryMetadata(LibnameIn=work, DsnOut=WorkDataSetCounts, Metadata=memname RecordCount) * Report on the number of observations for each data that begin with the letters, case insensitive, "NAME". This illustrates the use of a regular GetLibraryMetadata filter.; %GetLibraryMetadata(LibnameIn=work, DsnOut=DataSetNameCounts, Metadata=memname RecordCount, RegExFilter_1=/^NAME/I, Debug=N) * Override the default settings for the "Metadata" parameter to report on memname, RecordCount, as well as crdate (date set creation date, modate (each data set last modification date), nvar (number of variables in each data set) and ColumnNames (names of all columns in each data set) %GetLibraryMetadata(LibnameIn=work, DsnOut=Work_Metadata, Metadata= memname RecordCount crdate modate nvar ColumnNames, RegExFilter=/./I, Debug=N) * Report on all the metadata types available in SAS 9.3 in Dictionary.Tables, as well as RecordCount and ColumnNames. %GetLibraryMetadata(LibnameIn=work, DsnOut=Work_Metadata_Complete, Metadata= libname memname memtype dbms_memtype memlabel typemem crdate modate nobs obslen nvar protect compress encrypt npage filesize pcompress reuse bufsize delobs nlobs maxvar maxlabel maxgen gen attr indxtype datarep sortname sortchar reqvector datarepname encoding audit audit_before audit_admin audit_error audit_data num_character num_numeric RecordCount ColumnNames) * An example of a situation when there are no data sets that have a name that meets the requirements of the regular expression filter.; %GetLibraryMetadata(LibnameIn=work, DsnOut=DataSetNameCounts, RegExFilter_1=/^___$/I) */