Hi everyone.
I am having some problems with merging two data sets - variables and cases are different.
The data sets look like these:
Sample Data set 1 contains the following variables:
Respondent Entry_date Level (1,2,3,4,5) Decision (1,2)
1 01-Mar-2015 1 1
1 31-Jul-2016 2 2
2 05-Jan-2018 1 1
3 10-Aug-2015 4 1
4 02-Feb-2015 1 2
4 26-May-2017 1 2
4 21-Dec-2017 3 1
5 25-Dec-2016 5 2
A respondent can have multiple records. If respondent answers Level = 1, then respondent is also found in Data set 2 and answers variable DEGREE.
Sample data set 2:
Respondent Entry_date Degree (1,2,3,4) Awareness (1,2,3,4)
1 01-Mar-2015 2 4
2 05-Jan-2018 3 1
4 02-Feb-2015 1 2
4 26-May-2017 3 3
6 12-Aug-2015 4 1
6 04-Sep-2017 2 4
7 25-Dec-2016 1 2
In data set 2, respondents 6 and 7 do not belong to data set 1.
Thank you very much for your help.
In gestalt, your sample input and output looks like you want a full join on Responder and Entry_date. The sole exception are the rows 2 and 3 in the sample output which look as though as the values of Degree and Awareness in them have been swapped. The output row 2 with Responder=3 looks like you're matching Level from DS1 to Responder from DS2, yet the rest of the data completely voids this conjecture. Hence, if you swapped Degree and Awareness in output rows 2 and 3 by mistake, everything logically falls into place. If this is the case, the following will do what you need.
data ds1 ; input Respondent Entry_date:date. Level Decision ; format entry_date date9. ; cards ; 1 01-Mar-2015 1 1 1 31-Jul-2016 2 2 2 05-Jan-2018 1 1 3 10-Aug-2015 4 1 4 02-Feb-2015 1 2 4 26-May-2017 1 2 4 21-Dec-2017 3 1 5 25-Dec-2016 5 2 run ; data ds2 ; input Respondent Entry_date:date. Degree Awareness ; cards ; 1 01-Mar-2015 2 4 2 05-Jan-2018 3 1 4 02-Feb-2015 1 2 4 26-May-2017 3 3 6 12-Aug-2015 4 1 6 04-Sep-2017 2 4 7 25-Dec-2016 1 2 run ; proc sql ; create table want as select coalesce (ds1.respondent, ds2.respondent) as Respondent , coalesce (ds1.entry_date, ds2.entry_date) as Entry_date format=date11. , level, decision, degree, awareness from ds1 full join ds2 on ds1.respondent = ds2.respondent and ds1.entry_date = ds2.entry_date ; quit ;
The data set WANT would look like below. Check it that's what you really want.
Respondent Entry_date Level Decision Degree Awareness --------------------------------------------------------------------- 1 01-MAR-2015 1 1 2 4 1 31-JUL-2016 2 2 . . 2 05-JAN-2018 1 1 3 1 3 10-AUG-2015 4 1 . . 4 02-FEB-2015 1 2 1 2 4 26-MAY-2017 1 2 3 3 4 21-DEC-2017 3 1 . . 5 25-DEC-2016 5 2 . . 6 12-AUG-2015 . . 4 1 6 04-SEP-2017 . . 2 4 7 25-DEC-2016 . . 1 2
Paul D.
What do you mean by this:
variables and cases are different.
Nothing shown in your sample data indicates this, so if that exists any solution we post may not work. Is the sample data reflective of your actual data?
@yoyong wrote:
Hi everyone.
I am having some problems with merging two data sets - variables and cases are different.
The data sets look like these:
Sample Data set 1 contains the following variables:
Respondent Entry_date Level (1,2,3,4,5) Decision (1,2)
1 01-Mar-2015 1 1
1 31-Jul-2016 2 2
2 05-Jan-2018 1 1
3 10-Aug-2015 4 1
4 02-Feb-2015 1 2
4 26-May-2017 1 2
4 21-Dec-2017 3 1
5 25-Dec-2016 5 2
A respondent can have multiple records. If respondent answers Level = 1, then respondent is also found in Data set 2 and answers variable DEGREE.
Sample data set 2:
Respondent Entry_date Degree (1,2,3,4) Awareness (1,2,3,4)
1 01-Mar-2015 2 4
2 05-Jan-2018 3 1
4 02-Feb-2015 1 2
4 26-May-2017 3 3
6 12-Aug-2015 4 1
6 04-Sep-2017 2 4
7 25-Dec-2016 1 2
In data set 2, respondents 6 and 7 do not belong to data set 1.
Thank you very much for your help.
@Reeza Some variables may be found on both data sets (Respondent and Entry_date). But there are variables unique to each data set (Level and Decision for data set 1 and Degree and Awareness for data set 2). Similarly, some respondents (cases) can be found on both data sets (Respondents 1 and 4). But there are respondents who are unique to each data set (e.g. Respondents 3 and 5 are only in Data set 1 while Respondents 6 and 7 are in Data set 2) .
Show what you intend the resulting data set to look like.
In gestalt, your sample input and output looks like you want a full join on Responder and Entry_date. The sole exception are the rows 2 and 3 in the sample output which look as though as the values of Degree and Awareness in them have been swapped. The output row 2 with Responder=3 looks like you're matching Level from DS1 to Responder from DS2, yet the rest of the data completely voids this conjecture. Hence, if you swapped Degree and Awareness in output rows 2 and 3 by mistake, everything logically falls into place. If this is the case, the following will do what you need.
data ds1 ; input Respondent Entry_date:date. Level Decision ; format entry_date date9. ; cards ; 1 01-Mar-2015 1 1 1 31-Jul-2016 2 2 2 05-Jan-2018 1 1 3 10-Aug-2015 4 1 4 02-Feb-2015 1 2 4 26-May-2017 1 2 4 21-Dec-2017 3 1 5 25-Dec-2016 5 2 run ; data ds2 ; input Respondent Entry_date:date. Degree Awareness ; cards ; 1 01-Mar-2015 2 4 2 05-Jan-2018 3 1 4 02-Feb-2015 1 2 4 26-May-2017 3 3 6 12-Aug-2015 4 1 6 04-Sep-2017 2 4 7 25-Dec-2016 1 2 run ; proc sql ; create table want as select coalesce (ds1.respondent, ds2.respondent) as Respondent , coalesce (ds1.entry_date, ds2.entry_date) as Entry_date format=date11. , level, decision, degree, awareness from ds1 full join ds2 on ds1.respondent = ds2.respondent and ds1.entry_date = ds2.entry_date ; quit ;
The data set WANT would look like below. Check it that's what you really want.
Respondent Entry_date Level Decision Degree Awareness --------------------------------------------------------------------- 1 01-MAR-2015 1 1 2 4 1 31-JUL-2016 2 2 . . 2 05-JAN-2018 1 1 3 1 3 10-AUG-2015 4 1 . . 4 02-FEB-2015 1 2 1 2 4 26-MAY-2017 1 2 3 3 4 21-DEC-2017 3 1 . . 5 25-DEC-2016 5 2 . . 6 12-AUG-2015 . . 4 1 6 04-SEP-2017 . . 2 4 7 25-DEC-2016 . . 1 2
Paul D.
Ok, none of that is problematic at all, a standard merge or join will work. Have you tried a merge/join and it didn't work for some reason.
First sort each data set by the common variables you want to join on and then merge. For variables that are the same, they will get overwritten if not the join variables. You can control what ends up in the final data set using IN options.
proc sort data=have1; by your_variables_here; run;
proc sort data=have2; by your_variables_here; run;
data want;
merge have1 (in=t1) have2 (in=t2);
by your_variables_here;
if t1 and t2 then status='Both';
else if t1 then status='Table1';
else if t2 then status='Table2';
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.