SAS: How to Use UNION in PROC SQL


You can use the UNION operator in the PROC SQL statement in SAS to combine two datasets vertically.

The following example shows how to use the UNION operator in practice.

Example: Using UNION in PROC SQL in SAS

Suppose we have the following dataset in SAS that contains information about various basketball players:

/*create first dataset*/
data data1;
    input team $ points;
    datalines;
A 12
A 14
A 15
A 18
A 20
A 22
;
run;

/*view first dataset*/
proc print data=data1;

And suppose we have another dataset in SAS that also contains information about various basketball players:

/*create second dataset*/
data data2;
    input team $ points;
    datalines;
A 12
A 14
B 23
B 25
B 29
B 30
;
run;

/*view second dataset*/
proc print data=data2;

We can use the UNION operator in the PROC SQL statement to combine these two datasets vertically and only keep the unique rows:

/*combine tables vertically and only keep unique rows*/
proc sql;
   title 'data1 UNION data2';
   select * from data1
   union
   select * from data2;
quit;

Notice that the two datasets have been combined vertically and only the unique rows are kept.

We can also use the UNION ALL operator in the PROC SQL statement to combine these two datasets vertically and keep all of the rows:

/*combine tables vertically and keep all rows*/
proc sql;
   title 'data1 UNION ALL data2';
   select * from data1
   union all
   select * from data2;
quit;

Notice that the two datasets have been combined vertically and all rows are kept from both datasets, even the ones that are duplicates.

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

How to Calculate Z-Scores in SAS
How to Use Proc Summary in SAS
How to Calculate Mean, Median, & Mode in SAS

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *