w. Patrick Gale

Design, photography and ramblings

Month: June 2015

Get all dataset variables in your SAS library

Below is a useful SAS program to query and retrieve all datasets, dataset variables, and variable details in a SAS library.

libname MYREP "C:\my_SAS_repository";
proc sql noprint;
create table ds as
select * from dictionary.tables
where libname="MYREP";
quit;
/*get variable information*/;
proc sql noprint;
create table cont as
select * from dictionary.columns
where libname="MYREP";
quit;

SAS – search library or selected datasets

Below is modified code from http://support.sas.com/kb/33/078.html (used to search your SAS library of datasets for a string and get a list of the datasets and variables where the string appears).  In line 49 of the original code, they hard-code the library name ‘test’ into the macro.  I changed the code so you use the library name passed to the macro and not hard-code the library name in the macro itself (code change highlighted in yellow ).

Another problem I had to resolve with the macro was when my dataset or dataset variable name contained spaces or was not a valid SAS member name.  To resolve this I had to wrap the macro variables in ” “n (see this highlighted below).

library MYLIBNAME "C://mySASrepository";  /* define your SAS library location if you have not already */

options nomprint nomlogic nosymbolgen nonotes;
/* find data sets names and store in a macro variable */

%macro grep(librf,string);  /* parameters are unquoted, libref name, search string */
%let librf = %upcase(&librf);
  proc sql noprint;
    select left(put(count(*),8.)) into :numds
    from dictionary.tables
    where libname="&librf";

    select memname into :ds1 - :ds&numds
    from dictionary.tables
    where libname="&librf";

  %do i=1 %to &numds;
    proc sql noprint;
    select left(put(count(*),8.)) into :numvars
    from dictionary.columns
    where libname="&librf" and memname="&&ds&i" and type='char';

    /* create list of variable names and store in a macro variable */
    %if &numvars > 0 %then %do;
      select name into :var1 - :var&numvars 
      from dictionary.columns
      where libname="&librf" and memname="&&ds&i" and type='char';
      quit;
		
      data _null_;
        set &librf.."&&ds&i"n;    /* wrapping &&ds&i in quotes to handle dataset names that are not valid SAS member names */
          %do j=1 %to &numvars;
              /* wrapping "&&var&j"n in quotes to handle variables that are not valid SAS member names and using the library reference (librf) we passed into the macro instead of hard coding the library name into the macro */
            /* if "&&var&j"n = "&string" then /* searches the variables for a specific value */
            if index("&&var&j"n,"&string") ge 1 then /* searches the variables for values 'LIKE' the input string */
            put "String &string found in dataset &librf..&&ds&i for variable &&var&j";
          %end;
        run;
    %end;
  %end; 
%mend;
/* search the MYLIBNAME library for variables that contain the string 'answer' */
%grep(MYLIBNAME,answer);

The code above will search your entire SAS library. But suppose you only wanted to search a few of the datasets? This simple change involves adding another argument called ‘datasets’ to the macro and added an additional query filter on memname. See below for modified code.

library MYLIBNAME "C://mySASrepository";  /* define your SAS library location if you have not already */

options nomprint nomlogic nosymbolgen nonotes;
/* find data sets names and store in a macro variable */

%macro grep(librf,string,datasets);  /* parameters are unquoted, libref name, search string */
%let librf = %upcase(&librf);
  proc sql noprint;
    select left(put(count(*),8.)) into :numds
    from dictionary.tables
    where libname="&librf" AND memname IN %upcase(&datasets);

    select memname into :ds1 - :ds&numds
    from dictionary.tables
    where libname="&librf" AND memname IN %upcase(&datasets);

  %do i=1 %to &numds;
    proc sql noprint;
    select left(put(count(*),8.)) into :numvars
    from dictionary.columns
    where libname="&librf" and memname="&&ds&i" and type='char';

    /* create list of variable names and store in a macro variable */
    %if &numvars > 0 %then %do;
      select name into :var1 - :var&numvars 
      from dictionary.columns
      where libname="&librf" and memname="&&ds&i" and type='char';
      quit;
		
      data _null_;
        set &librf.."&&ds&i"n;    /* wrapping &&ds&i in quotes to handle dataset names that are not valid SAS member names */
          %do j=1 %to &numvars;
              /* wrapping "&&var&j"n in quotes to handle variables that are not valid SAS member names and using the library reference (librf) we passed into the macro instead of hard coding the library name into the macro */
            /* if "&&var&j"n = "&string" then /* searches the variables for a specific value */
            if index("&&var&j"n,"&string") ge 1 then /* searches the variables for values 'LIKE' the input string */
            put "String &string found in dataset &librf..&&ds&i for variable &&var&j";
          %end;
        run;
    %end;
  %end; 
%mend;
/* search the MYLIBNAME library for variables that contain the string 'answer' ONLY within the "myDataset_13" and "myDataset_8" datasets (substitute your own dataset names here, and add any additional dataset names by separating with a comma) */
%grep(MYLIBNAME,answer, ("myDataset_13","myDataset_8"));

© 2017 w. Patrick Gale

Theme by Anders NorenUp ↑

Follow

Follow this website

Get every new post delivered right to your inbox.

Email address