Macro index sas or other?
I have 169 cities for which I want to iterate over the macro. I need the output files to be saved using the city name (not the city code). I have a dataset (TOWN) with city code and city name. Is it possible to have a% let statement that is given to the city name for each iteration where i = city code?
I know that I can list city names using the index function, but I would like to set the index function so that it specifies the% let TOWN.town-name statement when I = TOWN.town code.
All answers below seem to be possible. I used the% let =% scan (, & i) variant. The limitation is that city names can be more than one word, so I replaced the underscores for spaces, which I fix later.
This is my macro. I am displaying a progress report for each of the 169 cities. I need the excel file to be saved as city name and the title should include the city name. Then, in excel, I combine all 169 worksheets into one 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;
% FIX;
source to share
My guess is that the reason you want to find the name of the city by the city's zip code is to call the macro repeatedly with each city name. If so, then you don't even need to get involved in the city's business index at all. Just call the macro with each city name. There are many ways to do this. Here's one way: 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 need to search a table, one way is to convert the city names to numeric format and write a simple macro to get the name given the 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
*/
source to share
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;
source to share