macro do loop

[Myurathan]

Hi Folks,

I have following code, 

%macro test (num,country);
	%do i = 1 %to 3;
proc sql outobs=#
create table combined as
select distinct contractID, sourcesystemID, countryid, stage
from ifrs._09_abc
where countryid = &country. and stage = &i;

%end;

%mend;

%test(10,1);

When it ran it produces the table with only stage 3. I think the loop replaces the values. I want to have all three stages in the output table. 

Thanks in advance.

[Kurt_Bremser]

You always use the same name for the output table, so the second iteration overwrites the result from the first, and the third the second.

[RW9 · Accepted solution]

Then you need to append the proc sql output to a dataset each loop, i.e.

data want;
  stop;
run;

%macro test (num,country);
  %do i = 1 %to 3;
    proc sql outobs=#
      create table combined as
      select distinct contractID, 
                      sourcesystemID, 
                      countryid, 
                      stage
      from            ifrs._09_abc
       where          countryid =&country. 
             and      stage=&i;
    quit;
    data want;
      set want combined;
    run;
  %end;
%mend;

%test(10,1);

So each iteration the output combined is added to want.

That being said, I don't see any point for this code at all, simply:

%let country=1;
%let stage=3;

proc sort data=ifrs._09_abc out=want (keep=contractid...) nodupkey;
  by contractid...;
  where countryid=&country. and stage <= &stage.;
run;

Note add your other variables in where the ... is.

[Myurathan]

Thanks RW9. 

I need this code as I want 10 rows of output for each stages. So each country will have 30 rows of data.

[MadhuKorni]

In the first iteration  you are creating a table Combined for Stage1 data.

In the next iteration you are creating a table for stage2 data with the same table name Combined which overrides the first table.

The third iteration creates a table Combined which overrides the table Combined which has Stage2 data.

So you are left with Stage3 data.

If you want to create 3 tables for 3 values you can use 
create table Combined&i as ...... and append the tables

%macro test (num,country);
%do i = 1 %to 3;

proc sql outobs=#
create table combined&i as
select distinct contractID, sourcesystemID, countryid, stage
from ifrs._09_abc
where countryid = &country. and stage = &i;
quit;

proc append base= combined data = combined&i;run;

%end;

%mend;

%test(10,1);