Hello,
I have a list of data sets that follow the same naming convention: map_data_1718, map_data_1617, map_data_1516 . . . , map_data_0203. The below loop works for reading in and creating working data sets for all the raw data sets in the list:
%macro iterm(lst); %local finish; %local i; %local year; %let finish=%sysfunc(countw(&lst)); %do i = 1 %to &finish; %let year=%scan(&lst,&i); proc import datafile="&maindir.\map_data_&year..csv" out=work.over12_&year. dbms=csv replace; run; %end; %mend iterm; %iterm(1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203)
However, there are actually more data sets than this. Not only are there the "map_data_XXXX" data sets, but there are map12to17_XXXX and map18older_XXXX data sets. All sets of data sets follow the same numeric year suffix pattern. So I am hoping to have a loop within a loop that reads in ALL the data sets. That is, for each prefix in map_data, map12to17, map18older AND for each year in 1718, 1617, . . . 0203, read-in and create a working data set. Specifically, the data set are map_data_1718, map_data_1617 . . . map_data_0203; map12to17_1718, map12to17_1617 . . . map12to17_0203; map18older1718, map18older1617 . . . map18older0203.
I am newish to SAS and can't seem to figure out how to get the loop within a loop thing to work. Below is my attempt:
%macro jterm(list); %local fin; %local j; %local prefix; %let fin=%sysfunc(countw(&list)); %do j = 1 %to &fin; %let prefix=%scan(&list,&j); %macro iterm(lst); %local finish; %local i; %local year; %let finish=%sysfunc(countw(&lst)); %do i = 1 %to &finish; %let year=%scan(&lst,&i); proc import datafile="&maindir.\&prefix._&year..csv" out=work.over12_&year. dbms=csv replace; run; %end; %mend iterm; %end; %mend jterm; %iterm(1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203) %jterm(map_data map12to17 map18older)
Nested macros is rarely if ever a good idea. Nested loops ought to work just fine. This code is untested, but it should give you the idea.
%macro loop(list1,list2);
%let fin=%sysfunc(countw(&list1));
%let finish=%sysfunc(countw(&list2));
%do j = 1 %to &fin;
%let prefix=%scan(&list1,&j);
%do i = 1 %to &finish;
%let year=%scan(&list2,&i);
proc import datafile="&maindir.\&prefix._&year..csv"
out=work.over12_&year.
dbms=csv
replace;
run;
%end;
%end;
%mend loop;
%loop(map_data map12to17 map18older,1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203)
Nested macros is rarely if ever a good idea. Nested loops ought to work just fine. This code is untested, but it should give you the idea.
%macro loop(list1,list2);
%let fin=%sysfunc(countw(&list1));
%let finish=%sysfunc(countw(&list2));
%do j = 1 %to &fin;
%let prefix=%scan(&list1,&j);
%do i = 1 %to &finish;
%let year=%scan(&list2,&i);
proc import datafile="&maindir.\&prefix._&year..csv"
out=work.over12_&year.
dbms=csv
replace;
run;
%end;
%end;
%mend loop;
%loop(map_data map12to17 map18older,1718 1617 1516 1415 1314 1213 1112 1011 0910 0809 0708 0607 0506 0405 0304 0203)
Make it with 1 macro and two loops:
%macro jterm(list, lst);
%local fin;
%local j;
%local prefix;
%let fin=%sysfunc(countw(&list));
%do j = 1 %to &fin;
%let prefix=%scan(&list,&j);
%local finish;
%local i;
%local year;
%let finish=%sysfunc(countw(&lst));
%do i = 1 %to &finish;
%let year=%scan(&lst,&i);
proc import datafile="&maindir.\&prefix._&year..csv"
out=work.over12_&year.
dbms=csv
replace;
run;
%end;
%end;
%mend jterm;
options mprint;
%jterm(1718 1617, map_data map12to17)
All the best
Bart
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.