How To Merge Two Data Sets In Sas

SAS - Merge Information Sets

Multiple SAS data sets can be merged based on a specific mutual variable to requite a single data set up. This is done using the MERGE statement and BY statement. The total number of observations in the merged data set is oftentimes less than the sum of the number of observations in the original data sets. It is because the variables form both data sets go merged equally one record based when there is a match in the value of the common variable.

At that place are two Prerequisites for merging data sets given beneath −

input information sets must take at least 1 common variable to merge on.
input data sets must be sorted by the common variable(s) that will be used to merge on.

Syntax

The bones syntax for MERGE and BY argument in SAS is −

MERGE Data-Ready ane Information-Set 2 BY Common Variable

Following is the description of the parameters used −

Data-set1,Data-set2 are data prepare names written i after another.
Common Variable is the variable based on whose matching values the data sets will exist merged.

Information Merging

Let us understand data merging with the help of an case.

Example

Consider two SAS data sets i containing the employee ID with proper noun and bacon and another containing employee ID with employee ID and department. In this case to get the consummate information for each employee we can merge these two data sets. The final data set will still accept ane observation per employee but it will incorporate both the salary and department variables.

# Data set 1	 ID NAME SALARY	 1 Rick 623.3		  2 Dan 515.two 		 3 Mike 611.five 		 four Ryan 729.1  5 Gary 843.25  half dozen Tusar 578.6  7 Pranab 632.8  viii Rasmi 722.5   # Data set 2 ID DEPT 1 IT  two OPS 3 Information technology  4 HR  five FIN  six Information technology  vii OPS 8 FIN   # Merged information set up ID Name SALARY DEPT	 i Rick 623.3	IT 		  ii Dan 515.2 	OPS	 three Mike 611.5 	It 	 4 Ryan 729.i    HR  5 Gary 843.25   FIN  6 Tusar 578.half-dozen   It  seven Pranab 632.8  OPS 8 Rasmi 722.5   FIN

The to a higher place effect is achieved by using the following code in which the common variable (ID) is used in the By statement. Please notation that the observations in both the datasets are already sorted in ID cavalcade.

Information SALARY;     INPUT empid name $ salary  ;  DATALINES;  1 Rick 623.3		  2 Dan 515.two 		 3 Mike 611.five 		 iv Ryan 729.ane  5 Gary 843.25  half dozen Tusar 578.half dozen  seven Pranab 632.eight  8 Rasmi 722.five  ; RUN;  Data DEPT;     INPUT empid dEPT $ ;  DATALINES;  ane It  2 OPS 3 IT  4 HR  5 FIN  6 It  seven OPS 8 FIN  ; RUN;  Information All_details; MERGE SALARY DEPT; Past (empid); RUN; PROC PRINT Information = All_details;  RUN;

Missing Values in the Matching Cavalcade

In that location may be cases when some values of the common variable will not lucifer between the data sets. In such cases the information sets still get merged simply requite missing values in the issue.

Example

Consider the case of employee ID 3 missing from the dataset salary and employee ID 6 missing form information prepare DEPT. When the above lawmaking is applied, we get the below result.

ID NAME Bacon DEPT	 ane Rick 623.iii	IT 		  2 Dan 515.2 	OPS	 iii .		.		IT four Ryan 729.1    HR  v Gary 843.25   FIN  6 Tusar 578.6   . 7 Pranab 632.8  OPS viii Rasmi 722.five   FIN

Merging simply the Matches

To avoid the missing values in the effect we can consider keeping only the observations with matched values for the common variable. That is achieved past using the IN statement. The merge statement of the SAS program needs to be changed.

Example

In the beneath instance, the IN= value keeps simply the observations where the values from both the data sets SALARY and DEPT match.

DATA All_details; MERGE Bacon(IN = a) DEPT(IN = b); BY (empid); IF a = 1 and b = ane; RUN; PROC PRINT Information = All_details;  RUN;

Upon execution of the above SAS program with the to a higher place inverse part, we get the following output.

1 Rick 623.3	IT 		  2 Dan 515.2 	OPS	 iv Ryan 729.1    HR  five Gary 843.25   FIN  seven Pranab 632.8  OPS 8 Rasmi 722.5   FIN

Useful Video Courses

Video