BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gargi01
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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