sas macro index or other?

Question

I have 169 towns for which I want to iterate a macro. I need the output files to be saved using the town-name (rather than a town-code). I have a dataset (TOWN) with town-code and town-name. Is it possible to have a %let statement that is set to the town-name for each iteration where i=town-code?

I know that I can list out the town-names using the index function, but I'd like a way to set the index function so that it sets a %let statement to the TOWN.town-name when i=TOWN.town-code.

---

All the answers below seem possible. I have used the %let = %scan( ,&i) option for now. A limitation is that the town names can be more than one word, so I've substituted underscores for spaces that I correct later.

This is my macro. I output proc report to excel for each of the 169 towns. I need the excel file to be saved as the name of the town and for the header to include the name of the town. Then, in excel, I merge all 169 worksheets into a single workbook.

%MACRO BY_YEAR;

%let townname=Andover Ansonia Ashford Avon ... Woodbury Woodstock;

%do i = 1999 %to 2006;

%do j = 1 %to 169;

%let name = %scan(&townname,&j); 

ods tagsets.msoffice2k file="&ASR.\Town_Annual\&i.\&name..xls" style=minimal;


proc report data=ASR nofs nowd split='/';
where YR=&i and TWNRES=&j;
  column CODNUM AGENUM  SEX,(dths_sum asr_sum seasr_sum);
  define CODNUM / group     ;
  define agenum / group     ;
  define sex / across ;
  define dths_sum / analysis ;
  define asr_sum / analysis ;
  define seasr_sum / analysis ;
  break after CODNUM / ul;
  TITLE1 "&name Resident Age-Specific Mortality Rates by Sex, &i";
  TITLE2 "per 100,000 population for selected causes of death";
run;

ods html close;

%end;

%end;

%MEND;

Answer 1

My guess is that the reason why you want to look up the town name by town index is to repeatedly call a macro with each town name. If this is the case, then you don't even need to get involved with the town index business at all. Just call the macro with each town name. There are many ways to do this. Here is one way using call execute().

data towns;
  infile cards dlm=",";
  input town :$char10. @@;
cards;
My Town,Your Town,His Town,Her Town
;
run;

%macro doTown(town=);
  %put Town is &town..;
%mend doTown;

/* call the macro for each town */
data _null_;
  set towns;
  m = catx(town, '%doTown(town=', ')');
  call execute(m);
run;

/* on log
Town is My Town.
Town is Your Town.
Town is His Town.
Town is Her Town.
*/

If you do need to do a table lookup, then one way is to convert your town names into a numeric format and write a simple macro to retrieve the name, given an index value. Something like:

data towns;
  infile cards dlm=",";
  input town :$char10. @@;
cards;
My Town,Your Town,His Town,Her Town
;
run;

/* make a numeric format */
data townfmt;
  set towns end=end;
  start = _n_;
  rename town = label;
  retain fmtname 'townfmt' type 'n';
run;
proc format cntlin=townfmt;
run; 

%macro town(index);
  %trim(%sysfunc(putn(&index,townfmt)))
%mend town;

%*-- check --*;
%put %town(1),%town(2),%town(3),%town(4);
/* on log
My Town,Your Town,His Town,Her Town
*/

Answer 2

Or how about you just pass both the code and the name to the macro as parameters? Like this?

%MACRO DOSTUFF(CODE=, NAME=);
DO STUFF...;
PROC EXPORT DATA=XYZ OUTFILE="&NAME."; RUN;
%MEND;

DATA _NULL_;
SET TOWNS;
CALL EXECUTE("%DOSTUFF(CODE=" || STRIP(CODE) || ", NAME=" || STRIP(NAME) || ");");
RUN;