DATA Step, Macro, Functions and more

macro do loop

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

macro do loop

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.

 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,813

Re: macro do loop

Posted in reply to Myurathan

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.

 

View solution in original post


All Replies
Super User
Posts: 10,534

Re: macro do loop

Posted in reply to Myurathan

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
3 weeks ago
Super User
Super User
Posts: 9,813

Re: macro do loop

Posted in reply to Myurathan

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.

 

Contributor
Posts: 20

Re: macro do loop

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.

 

Frequent Contributor
Posts: 79

Re: macro do loop

[ Edited ]
Posted in reply to Myurathan

 

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);

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 90 views
  • 2 likes
  • 4 in conversation