SAS OUTER UNION CORR by Using Do Loop

[jhusoc]

Hello all,
I need to find a way to use loop to union my datasets.
For example, I have three tables with different column names. I want to union them all by using "outer nuion corr".
The code like this:

proc sql;
create table new as
select * from a1
outer union corr
select * from b1
outer union corr
select * from c1 ;
quit;

I want to use the loop function to do these unions.

%macro union_a;
proc sql;
create table new as
select * from a1
%do i = 2 %to 3;
outer union corr select * from a&i
;
quit;

%end;
%mend;
%union_a;

it gives me an error:

NOTE: Line generated by the invoked macro "UNION_A".
5 outer union corr select * from a&i ;
-----
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

How should I do this? Thank you!

JH

[Tom · Accepted solution]

Your %END is in the wrong place.

[jhusoc]

Oh yes! Thank you!

[Reeza]

SET works much easier and better here. 

data want;
set a1 b1 c1;
run;

---

@jhusoc wrote:

Hello all,
I need to find a way to use loop to union my datasets.
For example, I have three tables with different column names. I want to union them all by using "outer nuion corr".
The code like this:

proc sql;
create table new as
select * from a1
outer union corr
select * from b1
outer union corr
select * from c1 ;
quit;

I want to use the loop function to do these unions.

%macro union_a;
proc sql;
create table new as
select * from a1
%do i = 2 %to 3;
outer union corr select * from a&i
;
quit;

%end;
%mend;
%union_a;

it gives me an error:

NOTE: Line generated by the invoked macro "UNION_A".
5 outer union corr select * from a&i ;
-----
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

How should I do this? Thank you!

JH

---