topic Re: WARNING: A table has been extended with null columns to perform the UNION set operation. in New SAS User

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

bhanuprakash — Wed, 24 Jul 2019 17:20:27 GMT

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 ;

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

Kurt_Bremser — Wed, 24 Jul 2019 17:26:00 GMT

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.

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

bhanuprakash — Wed, 24 Jul 2019 17:28:58 GMT

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.

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

novinosrin — Wed, 24 Jul 2019 17:29:53 GMT

Hello @bhanuprakash

Try Outer union corr


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

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

novinosrin — Wed, 24 Jul 2019 17:33:33 GMT

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

data want;

set a b;

run;

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

bhanuprakash — Wed, 24 Jul 2019 17:42:54 GMT

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

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

novinosrin — Wed, 24 Jul 2019 17:53:27 GMT

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.

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!

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

bhanuprakash — Wed, 24 Jul 2019 18:00:05 GMT

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

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

Tom — Wed, 24 Jul 2019 18:25:56 GMT

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;

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

Kurt_Bremser — Thu, 25 Jul 2019 06:30:05 GMT

@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.