How to Use SAS - Lesson 4 - Merging Data Sets

Bitcoin donations are welcome: 1GGV3gbJeA83FWmz9hDfPri8EuqcUtodXy

Mike's SAS Tutorials Lesson 4

This video series is intended to help you learn how to program using SAS for your statistical needs. Lesson 4 introduces the concept of merging SAS data sets using a variety of methods. I discuss how one can merge two or more data sets in the DATA STEP using the SET statement. I also describe how one can use the MERGE statement to bring two or more datasets together that may have a common index variable. Furthermore, I describe the SORT procedure (PROC SORT) that must be used with the MERGE statement. Finally, I provide basic methods of merging data sets using PROC SQL.

Helpful Notes:
1. Use one SET statement when you have the same variables, but different observations.

2. Use two SET statements when you have different variables, but the same observations.

3. Use the MERGE statement when you have a common index variable, and any new variables or observations.

4. The MERGE statement first requires that you use the SORT procedure (PROC SORT) to sort on the index variable before merging.

5. Make sure that you add the BY statement after the MERGE statement in your DATA step or you will have a new dataset that is merged incorrectly.

6. PROC SQL is an advanced method of merging data that can be very powerful for large datasets. It uses different kinds of "JOINS" that I will provide more information on in a later video.

Today's Code:
data main;
input x y z;
cards;
1 2 3
7 8 9
;
run;

/* 1. Use one SET statement when you have the same variables, but different observations */
data more_people;
input x y z;
cards;
4 5 6
3 6 9
;
run;

data final;
set main more_people;
run;

proc print data=final; run;

/* 2. Use two SET statements when you have different variables, but the same observations */
data more_vars;
input a b c;
cards;
20 40 60
10 20 30
;
run;
data new_final;
set main;
set more_vars;
run;

proc print data=new_final; run;

/* 3. Use the MERGE statement when you have a common index variable, and any new variables or observations */
data more_vars_and_people;
input x a b c;
cards;
1 20 40 60
7 10 20 30
2 11 12 13
3 14 15 16
;
run;

* The MERGE statement requires that you use an index variable to merge on (e.g. an ID variable).;
* Thus, you must SORT your data BY that index variable.;
proc sort data=main;
by x;
proc sort data=more_vars_and_people;
by x;
run;
data merged_final;
merge main more_vars_and_people;
by x;
run;

proc print data=merged_final; run;


/* 4. SQL is an advanced programming language for databases.
Here, I provide a basic example to merge the two datasets using a LEFT JOIN.
I will include more information about JOIN types in a follow up video.
For now, think of a LEFT JOIN as one that only includes the data from the second dataset (more_vars_and_people)
that corresponds to data from the original dataset (main).
*/
proc sql;
create table sql_final as
select L.*, R.*
from main as L
LEFT JOIN more_vars_and_people as R
on L.x = R.x;
quit;

proc print data=sql_final; run;