For the most part, experienced SAS programmers know where to look for the source data they need. In the pharmaceutical industry, we are familiar with CDISC standards and data structures.

However, should the data standard be unfamiliar or the source datasets include new or unusual parameters, it may be prudent to have SAS look through the data on your behalf to save time.

We can do this by making use of PROC CONTENTS and SAS macro loops with the forward re-scan rule. If you’re not familiar with the forward re-scan rule, see my other blog post covering the topic.

First, let us define the two input parameters we’ll need: the library we’re delving into, and the character string we’re looking for. These are the only two parameters the end user needs to edit.

/*Search parameters - LIBRARY and text to search for*/
%let lib = %str(sashelp);
%let searchtext = %str(cholesterol);

Next, we’ll find all the datasets in the library specified above by running PROC CONTENTS and packing the results into iterable macro variables. In this case we can allocate a maximum of 999 datasets, but you can increase this value as needed.

/*Find all datasets within the library and allocate into numbered macro variables DS1, DS2, ...*/
proc contents data=&lib.._all_ out=members noprint;
run;

proc sql noprint;
    select distinct memname into :ds1-:ds999 from members;
quit;
%let ds_num = &sqlobs.;

Specify an empty results dataset. As matches are found during iteration, they will be appended to this dataset.

/*Clear the results dataset. As matches are found, they will be appended here.*/
data results;
    set _null_;
run;

Next we will loop over the datasets allocated as DS1, DS2, DS… above. The logical outline of the loop is as follows:

/*The macro logic for looping over each dataset in DS1, DS2, ...*/
%macro loop;
    %do i = 1 %to &ds_num.;
        /*For each dataset in the library, output its metadata contents*/

        /*Check the metadata for matches of the search text. This includes the dataset name, as well as the column names and labels.*/

        /*Check the actual data values for matches of the search text.*/

        /*For the current dataset, set the metadata and row value results.*/

        /*For the output dataset append the results of each loop iteration.*/
    %end;
%mend loop;
%loop;

For each step above, detailed logic follows below:

/*For each dataset in the library, output its metadata contents*/
proc contents data=&lib..&&ds&i out=c noprint;
run;

/*Check the metadata for matches of the search text. This includes the dataset name, as well as the column names and labels.*/
data metadata;
    length msg $500. dataset varname varlabel $100.;
    set c;
            
    /*Dataset name checking*/
    if index(upcase(memname), upcase("&searchtext.")) > 0 then do;
        msg = "Dataset: Found &searchtext.";
        dataset = "&&ds&i";
        varname = "";
        varlabel = "";
        output;
    end;

    /*Column name and label checking*/
    if index(upcase(name), upcase("&searchtext.")) > 0 or index(upcase(label), upcase("&searchtext.")) > 0 then do;
        msg = "Col Meta: Found &searchtext.";
        dataset = "&&ds&i";
        varname = name;
        varlabel = label;
        output;
    end;
            
    keep dataset varname varlabel msg;
            
    /*Get rid of duplicate messages in the case of multiple identical matches.*/
    proc sort nodupkey; by msg dataset varname varlabel;
run;

/*Check the actual data values for matches of the search text.*/
data actualdata;
    length msg $500. dataset varname varlabel varval $100.;
    set &lib..&&ds&i;
            
    /*Create an array of all character variables in the current dataset.*/
    array allChar _CHARACTER_;

    /*Loop over each character column and check for matches of the search text.*/
    do over allChar;
        if index(upcase(allchar), upcase("&searchtext.")) > 0 then do;
            msg = "Value: Found &searchtext.";
            dataset = "&&ds&i";
            varname = vname(allchar);
            varlabel = vlabel(allchar);
            varval = allchar;
            output;
        end;
    end;
    keep dataset varname varlabel varval msg;
            
    /*Get rid of duplicate messages in the case of multiple identical matches.*/
    proc sort nodupkey; by msg dataset varname varlabel varval;
run;

/*For the current dataset, set the metadata and row value results.*/
data allmsgs;
    set metadata actualdata;
    msg = upcase(msg);
run;

/*For the output dataset, OUT, keep appending the results of each loop iteration.*/
data results;
    set 
        results 
        allmsgs
    ;
run;

The steps above conclude the inner logic of the loop. Finally, outside the loop, we can clean up the datasets we no longer need.

/*Clean up*/
proc datasets library=work nolist;
    delete members c metadata actualdata allmsgs;
quit;

We started off with the SASHELP library, looking for the string “cholesterol”. These are the results:

Since we are searching in SASHELP, the results also include values from the SAS helper datasets, i.e., VCOLUMN and VMACRO.

This search function is not case sensitive. Optimise and implement as needed for your particular scenario.

---

Filed under: Programming,SAS - @ October 29, 2020 15:22

Tags: datasets, find, libraries, library, sas, string