topic Re: Merging two data sets with different variables and different cases in SAS Programming

Merging two data sets with different variables and different cases

yoyong — Thu, 19 Jul 2018 19:46:54 GMT

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.

Re: Merging two data sets with different variables and different cases

Reeza — Thu, 19 Jul 2018 20:11:11 GMT

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.

Re: Merging two data sets with different variables and different cases

yoyong — Thu, 19 Jul 2018 20:32:59 GMT

@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) .

Re: Merging two data sets with different variables and different cases

mkeintz — Thu, 19 Jul 2018 20:47:19 GMT

Show what you intend the resulting data set to look like.

Re: Merging two data sets with different variables and different cases

Reeza — Thu, 19 Jul 2018 20:56:16 GMT

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;

Re: Merging two data sets with different variables and different cases

yoyong — Thu, 19 Jul 2018 21:13:13 GMT

@mkeintz I would like the result to look like this.

Thanks.

Re: Merging two data sets with different variables and different cases

hashman — Fri, 20 Jul 2018 03:47:00 GMT

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.

Re: Merging two data sets with different variables and different cases

yoyong — Mon, 23 Jul 2018 00:22:56 GMT

Thank you for the solutions!