DATA Step, Macro, Functions and more

SAS SQL Union set operator

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

SAS SQL Union set operator

Could someone please explain me how does the Union set operator work. Does it remove duplicates only from each individual dataset or does it remove overall duplicates from the resultant dataset as well?

I have read that while joining two datasets vertically using Union operator, the duplicates are dropped.

But when I used it to join two datasets, I got the duplicate rows in the final dataset!

Thanks in advance.


Accepted Solutions
Solution
‎01-31-2017 11:29 PM
Super User
Posts: 7,766

Re: SAS SQL Union set operator

It removes duplicates:

data test1;
set sashelp.class (obs=5);
run;

data test2;
set sashelp.class (firstobs=5 obs=10);
run;

proc sql;
create table want as
select * from test1
union
select * from test2
;
quit;

proc print data=want noobs;
run;

See the result:

Name       Sex    Age    Height    Weight

Alfred      M      14     69.0      112.5
Alice       F      13     56.5       84.0
Barbara     F      13     65.3       98.0
Carol       F      14     62.8      102.5
Henry       M      14     63.5      102.5
James       M      12     57.3       83.0
Jane        F      12     59.8       84.5
Janet       F      15     62.5      112.5
Jeffrey     M      13     62.5       84.0
John        M      12     59.0       99.5

Now, if you use union all instead of just union, you get this:

Name       Sex    Age    Height    Weight

Alfred      M      14     69.0      112.5
Alice       F      13     56.5       84.0
Barbara     F      13     65.3       98.0
Carol       F      14     62.8      102.5
Henry       M      14     63.5      102.5
Henry       M      14     63.5      102.5
James       M      12     57.3       83.0
Jane        F      12     59.8       84.5
Janet       F      15     62.5      112.5
Jeffrey     M      13     62.5       84.0
John        M      12     59.0       99.5

Henry now appears two times.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-31-2017 11:29 PM
Super User
Posts: 7,766

Re: SAS SQL Union set operator

It removes duplicates:

data test1;
set sashelp.class (obs=5);
run;

data test2;
set sashelp.class (firstobs=5 obs=10);
run;

proc sql;
create table want as
select * from test1
union
select * from test2
;
quit;

proc print data=want noobs;
run;

See the result:

Name       Sex    Age    Height    Weight

Alfred      M      14     69.0      112.5
Alice       F      13     56.5       84.0
Barbara     F      13     65.3       98.0
Carol       F      14     62.8      102.5
Henry       M      14     63.5      102.5
James       M      12     57.3       83.0
Jane        F      12     59.8       84.5
Janet       F      15     62.5      112.5
Jeffrey     M      13     62.5       84.0
John        M      12     59.0       99.5

Now, if you use union all instead of just union, you get this:

Name       Sex    Age    Height    Weight

Alfred      M      14     69.0      112.5
Alice       F      13     56.5       84.0
Barbara     F      13     65.3       98.0
Carol       F      14     62.8      102.5
Henry       M      14     63.5      102.5
Henry       M      14     63.5      102.5
James       M      12     57.3       83.0
Jane        F      12     59.8       84.5
Janet       F      15     62.5      112.5
Jeffrey     M      13     62.5       84.0
John        M      12     59.0       99.5

Henry now appears two times.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: SAS SQL Union set operator

Union does not remove duplicates.  It merely appends the data in the subsequent step to the data in the current step.  Exactly the same as if you use set a b; in a datastep or proc append one set to another.

Super User
Posts: 7,766

Re: SAS SQL Union set operator

@RW9 I beg to differ. Please run my example.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: SAS SQL Union set operator

Posted in reply to KurtBremser
Super User
Posts: 7,766

Re: SAS SQL Union set operator

People usually use union all to avoid the performance penalty caused by the sort distinct (by all! variables) that scans for the duplicates.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 144 views
  • 1 like
  • 3 in conversation