topic macro do loop in SAS Programming

macro do loop

Myurathan — Fri, 27 Jul 2018 10:40:13 GMT

Hi Folks,

I have following code,

%macro test (num,country);
	%do i = 1 %to 3;
proc sql outobs=&num;
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.

Re: macro do loop

Kurt_Bremser — Fri, 27 Jul 2018 10:51:19 GMT

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.

Re: macro do loop

RW9 — Fri, 27 Jul 2018 10:55:19 GMT

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=&num;
      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.

Re: macro do loop

MadhuKorni — Fri, 27 Jul 2018 11:01:23 GMT

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=&num;
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);

Re: macro do loop

Myurathan — Fri, 27 Jul 2018 11:01:55 GMT

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.