WARNING: A table has been extended with null columns to perform the UNION set operation.

[bhanuprakash]

Hi Team,

I am getting the below warning when I execute the below mentioned SAS code. can you please tell me is there any standard way to overcome this issue.

WARNING: A table has been extended with null columns to perform the UNION set operation.

SAS Code: 

data a ;
a = 1 ;
b = 2 ;
run ;

data b ;
a = 3 ;
b = 4 ;
c = 1 ;
run ;

proc sql ;
select a.* from a
union
select b.* from b ;
quit ;

[Kurt_Bremser]

You always get the warning when the parts of a union do not have all columns in common.

Extend your code:

proc sql ;
select a.*, . as c from a
union
select b.* from b ;
quit ;

so you have matching columns.

[bhanuprakash]

Dear KurtBremser,

I am sorry to say this but I am not expecting technique as I already programmed the same way. However when we have 100 variables then this technique will be difficult.

can you please suggest any standard way.

[novinosrin · Accepted solution]

Hello @bhanuprakash 

Try Outer union corr

proc sql ;
select a.* from a
outer union corr
select b.* from b ;
quit ;

[Kurt_Bremser]

---

@bhanuprakash wrote:
Dear KurtBremser,

I am sorry to say this but I am not expecting technique as I already programmed the same way. However when we have 100 variables then this technique will be difficult.

can you please suggest any standard way.

---

If you simply want to stack datasets, the data step is the tool of choice:

data c;
set a b;
run;

As you can see, it provides the shortest code possible.

[novinosrin]

A safe bet is datastep regardless bcoz your PDV at compile time is a sweetheart

data want;

set a b;

run;

[bhanuprakash]

Is there chance to loose the quality in the ouput when I set/append the data by using the “outer union corr” procedure, apart from performance issue(processing time).

[novinosrin]

Quality is never compromised. However, there is something called "Best practices". So basically it's healthy to follow best practices so that our processes offer reading ease, easy to update, maintain etc.

Of course there are probably more than one way to accomplish things in SAS and yes we shall eventually get the result we want by doing in one way or another. However, that's when people like @hashman  @PGStats , @FreelanceReinh  , @data_null__  etc(sorry can't list all) make the striking distinction. The notes that they offer along with the code and the details. Holy Jesus! I don't like them at all (lol). I am just extremely jealous.

PS

I mentioned those names specifically ( coz they detail so much that i really wonder why they want to share so much) for you to follow them , read their posts, understand. Sooner or later you will start contributing. All the best!

[bhanuprakash]

LOL .... Thank you so much for the info

[Tom]

Adding the OUTER keyword will let you keep the extra column, but it also will potentially keep duplicate observations that normal set operations would exclude.

Play around and see what you actually want.

data test;
 set sashelp.class (obs=3);
 if _n_=1 then sex=' ';
run;


proc sql;
select name,age from test
union  
select name,age,sex from test
;
select name,age from test
union corr 
select name,age,sex from test
;
select name,age from test
outer union  
select name,age,sex from test
;
select name,age from test
outer union corr 
select name,age,sex from test
;
quit;