topic macro do loop problem in SAS Procedures

macro do loop problem

comeon2012 — Thu, 14 Jun 2012 14:00:43 GMT

I want to repeat each country's obs. for 31 times and the following statements run well.

%let country1=abudhabi;

%let country2=argentina;

%macro x;

data %do i =1 %to 31;

work.&country1&i

%end;;

set &country1;

run;

data %do i =1 %to 31;

work.&country2&i

%end;;

set &country2;

run;

%mend x;

%x;

But because I have tens of countries, I also want to apply the do loop on "countryj". The commands I want is something like the commands below but it failed. Can anyone give me some advice? Thanks.

%let country1=abudhabi;

%let country2=argentina;

%macro x;

%do j = 1 %to 2;

data %do i =1 %to 31;

work.&&country&j&i

%end;;

set &&country&j;

run;

%end;

%mend x;

%x;

Re: macro do loop problem

Astounding — Thu, 14 Jun 2012 14:10:29 GMT

Examine how the extra ampersand plays out.

&&country&i&j

The fist time through the loops, this becomes

&country11

And there is no such macro variable. You would need to take advantage of using a dot to end a macro variable name, changing this to:

&&country&i..&j

On a secondary note, it might be easier to read the names if you change them slightly:

&&country&i.._&j

Good luck.

Re: macro do loop problem

p12937 — Tue, 19 Jun 2012 13:58:39 GMT

Looks like you all set, but here is another solution:

/* If you already have a data set with all the country names then you don't need this data step */
/* Just use your countries data set in the proc sql step. */
/* Otherwise, create a data set with all of your countries */
data countries;
country='argentina';output;
country='abudhabi';output;
run;

proc sql noprint;
select count(*)
into :countries
from countries
;
quit;

%macro x;

%do i=1 %to &countries.;

proc sql noprint;
select country
into :country
from countries (firstobs=&i. obs=&i.)
;
quit;

%let country=%trim(%left(&country.));

/* You probably won't need this data step, I did just to make the code work */
data &country.;
country="&country.";
run;

data
%do j=1 %to 31;
work.&country.&j.
%end;
;
set &country.;
run;

%end;

%mend x;

%x;