SAS Macro to loop through datasets

Question

I have a bunch of datasets named after dates (for example, 20130102) in a library called taq. Normally when I tried to read in multiple datasets I would do set taq.cq_&yyyymmdd:;. So that if I input yyyymmdd as 201201, it will fetch all the datasets starting with the prefix 201201.

However, now I am trying to run a macro looping across all datasets. Ideally:

%MACRO EXAMPLE(DATE_VAR);
    DO WHILE(...);
      %ANOTHER_MACRO(SOMETHING);
    END;
%MEND;

If I input %EXAMPLE(201301); , it will call another macro which executes on every dataset starting with the prefix 201301.

I was wondering how to design a do loop to do that?

I have read some posts on SAS forum and StackOverflow. But it seems that they either run the macro on all of the datasets, or they require a manual input of a list of datasets that I am running on. I am trying to make the procedure automatic (that is, the program can detect all the datasets with a prefix itself.)

The best references for SAS tutorials and how to's are either lexjansen.com which are user written posts going back before SO existed, same with communities.sas.com. And, UCLA SAS tutorials are great as well, though a bit outdated. — Reeza

Tom Tom · Accepted Answer · 2017-05-30T15:24:19

Read the list of dataset names from the metadata. You could use DICTIONARY.MEMBERS (also available as SASHELP.VMEMBER). Or you could just use PROC CONTENTS to generate the list.

For example you might have a parameter to supply the libref that points to the datasets and other that is used to help select specific members. Once you have the list then generate the macro calls. For example you could use CALL EXECUTE() to generate the macro calls.

%macro example(libref,prefix);
  proc contents data=&libref.._all_ noprint out=contents; run;
  data _null_;
    set contents;
    by memname;
    if first.memname ;
    if upcase(memname) =: %upcase("&prefix");
    call execute(cats('%nrstr(%another_macro)(',memname,')'));
  run;
%mend;

Then for your example the call would be:

%example(taq,cq_201301)

SAS Macro to loop through datasets

1 Answers