How to trigger a macro for every data set containing a keyword

user_19646183 0
Viewed 54

I'm doing a crash course on SAS macros and I'm stuck at one exercise. I have to create a macro, that will create a proc contents tables for every data set, that contains a keyword. I know how to do that using call execute, but I need this using proc sql and %do loop. My attempt:

%macro contents(data=&syslast);
   proc contents data=&data; 
      title "&data"; 
   run;
%mend contents;

%macro ContentsAll(keyword);
    select libname||'.'||memname
        into :dsn1-
            from sashelp.vstabvw
                where upcase(memname) like %upcase("%quote(%)&&keyword%")
        ;
    quit;
    %do i=1 %to &sqlobs;
        %contents(data=&&dsn&i);
    %end;
%mend ContentsAll;

options mlogic mprint;
%ContentsAll(class);
options nomlogic nomprint;

I know there is some issue with a select statement, but I have no idea how to fix it. And where statement has an unprotected variable (my attempts at fixing it just break the where clause alltogether.

sas-macro sas
2 Answers

First of all, good job. It's so good that I'm almost sorry you're only missing the Proc SQL Statement :-)

%macro contents(data=&syslast);
   proc contents data=&data; 
      title "&data"; 
   run;
%mend contents;

%macro ContentsAll(keyword);
    proc sql noprint;
    select libname||'.'||memname
        into :dsn1-
            from sashelp.vstabvw
                where upcase(memname) like %upcase("%quote(%)&&keyword%")
        ;
    quit;
    %do i=1 %to &sqlobs;
        %contents(data=&&dsn&i);
    %end;
%mend ContentsAll;

options mlogic mprint;
%ContentsAll(class);
options nomlogic nomprint;
user_40449360

There is no need to create all of those macro variables. Just keep the list of names in actual data. You can use CALL EXECUTE() to generate the code you want to run for each member in the list.

Note that the variables LIBNAME and MEMNAME will already be in uppercase when pulled from the DICTIONARY.MEMBERS metadata that the view SASHELP.VSTABVW uses. But the user passing in a value for the KEYWORD parameter might not have entered uppercase letters.

%macro ContentsAll(keyword);
data _null_;
  set sashelp.vstabvw ;
  where memname contains "%qupcase(&keyword)" ;
  call execute(cats('%nrstr(%contents)(data=',libname,'.',memname,')'));
run;
%mend ContentsAll;
user_49655490
Related