SAS: Iterate over dataset names

Question

I have a sas program that merges two datasets containing information on a given city. The city name is part of the title of every dataset, e.g. for Atlanta:

data work.AtlantaComplete;
merge dir.Atlanta1 dir.Atlanta2;
by mergevar;
run;

I would like to run the merge on a long list of cities without having to make a separate .sas file for each one. With limited knowledge of SAS I tried the following:

%let city1 = Boston;
...
%let cityN = Miami;

%DO for i = 1 to N;

data work.city&i;
set dir.city&i.1 dir.city&i.2;
by mergevar;
run;

This produced several errors, the main one being that %DO statements must be inside a data step. This won't work for my task because the loop has to be defined before the first data step.

A solution that can be implemented within the sas program would be optimal, but I am also open to something like a Unix Bash shell script that provides each city one at a time as a system input to the sas program (similar to sys.argv in Python).

Thanks for your help

Answer 1

You have several small mistakes in your program. Executing a %do loop is normally done inside a macro. Also you dont use keyword "for" and need a % in front of the to. so try this:

%let city1 = Boston;
...
%let cityN = Miami;
%let N = 42; *or whatever your N is...;

%macro mergecities(citynumber);

%DO i = 1 %to &citynumber;
    data work.&&city&i;
       set dir.&&city&i dir.&&city&i;
       by mergevar;
    run;
%end;

%mend;

%mergecities(&N);

Instead of using the macrovariable citynumber you can directly use &N inside the do loop, but used with a parameter the macro is more flexible...

Answer 2

If you have numbered macro variables, you use &&varname&i to resolve them. Also, by having your cities in a dataset, you can create the macro variables off the back of it, rather than hard-coding them all (plus the count).

data cities ;
  input City $20. ;
  /* Create numbered macro variables based on _n_ */
  call symputx(cats('CITY',_n_),City) ;
  call symputx('N',_n_) ;
datalines ;
Atlanta
Boston
Chicago
Houston
Texas
;
run ;

%MACRO LOOP ;
  %DO I = 1 %TO &N ;
    data &&CITY&I..Complete ;
      merge dir.&&CITY&I..1 
            dir.&&CITY&I..2 ;
      by mergevar ;
    run ;
  %END ;
%MEND ;
%LOOP ;