Submit a list of datasets to proc summary

[SAShole]

Hi SAS friends,

     I have multiple datasets that i would like to send to proc summary. I can get the dataset names into a macro variable but can't quite figure out how to send it to proc summary. below is pseudo code, thank you.

data class1 class2;

  set sashelp.class;

run;

proc sql noprint;

  select memname into: list separated by ' '

  from dictionary.tables

  where libname = 'WORK' and memname contains 'CLASS';

quit;

proc summary data = &list.;

  var _numeric_;

  output out = &list._sum sum=;

run;

[art297 · Accepted solution]

Call execute is an easy way to do what you want.  e.g.:

data class1 class2;

  set sashelp.class;

run;

proc sql noprint;

  create table flist as

    select memname

       from dictionary.tables

        where libname = 'WORK' and

              memname contains 'CLASS';

quit;

data _null_;

  set flist;

    call execute("proc summary data ="||

      trim(memname)||"; var _numeric_;output out="||

      trim(memname)||"_sum sum=;run;");

run;

[TomKari]

Interesting!

Are there any limits to how many steps you can "push" into the SAS queue with CALL EXECUTE?

Tom

[Tom]

There might be, but I would suspect it is quite large.  If you are generating macro calls then use %NRSTR() so that only the call is pushed onto the stack and not all of the code that it generates. Note that this trick also solves some timing issues that can occur when macros are executed by SAS as they are pushed onto the stack by CALL EXECUTE, but the actual generated code is not called until after the current datastep tops.

You can avoid this by just writing the code to temporary file and including it instead of using CALL EXECUTE.  This has two other advantages.

1) PUT statement syntax is much more flexible for generating the code.

2) You can generate the file and view its contents to verify that you are generating the code properly.

filename code temp;

data _null_;

  set list;

  put ..... ;

run;

%inc code /source2 ;

[data_null__]

I wonder if there is any advantage to just using SASHELP.VTABLE in the data step with CALL EXECUTE. 

[art297]

DN: Obviously!  I didn't think about it as I was just following the OP's originally proposed steps but, like you ask, that proc sql run isn't necessary.

: If there are any such limits, I'm not aware of them.