topic Re: SAS OUTER UNION CORR by Using Do Loop in SAS Programming

SAS OUTER UNION CORR by Using Do Loop

jhusoc — Sun, 02 May 2021 16:16:22 GMT

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!

Re: SAS OUTER UNION CORR by Using Do Loop

Tom — Sun, 02 May 2021 16:20:09 GMT

Your %END is in the wrong place.

Re: SAS OUTER UNION CORR by Using Do Loop

jhusoc — Sun, 02 May 2021 16:25:31 GMT

Oh yes! Thank you!

Re: SAS OUTER UNION CORR by Using Do Loop

Reeza — Mon, 03 May 2021 03:15:28 GMT

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