Merging: How di I keep by-variables from both files

[terjeph]

I have two datasets, both including ID and day. I would like to merge them by ID and day but include the day variables from both files in the data I want, i.e. include them also when there is no match on the day variable.

data have;

input ID Day;

datalines;

1 1

1 2

1 3

;

Data have2;

Input ID Day;

1 .

1 2

1 3

;

Data want;

ID Day Day

1 1  .

1 2 2

1 3 3

[Kurt_Bremser · Accepted solution]

Your "want" dataset is impossible, as a dataset can not have two variables with the same name.

You probably want

proc sql;
create table want as
  select
    t1.id,
    t1.day as day_1,
    t2.day as day_2
  from have1 t1
  left join have2 t2
  on t1.id = t2.id and t1.day = t2.day
;
quit;

[terjeph]

Thanks! SQL worked well.

[Tom]

What happened to the first observation from the second dataset?

 You did not include it in your expected output.  Why not?

First let's clean up your example datasets so they actually can run:

data have;
  input ID Day;
datalines;
1 1
1 2
1 3
;
 
Data have2;
  input ID Day;
datalines;
1 .
1 2
1 3
;

data expect; 
  input ID Day1 Day2
datalines;
1 1  .
1 2 2
1 3 3
;

To tell if the a given dataset is contributing to the merge you can use the IN= dataset option.  Those variables are temporary so you can assign them to new variables if you want to remember them.

So perhaps something like this?

data want;
  merge have(in=in1) have2(in=in2);
  by id day;
  day1=in1;
  day2=in2;
run;

Result:

Obs    ID    Day    day1    day2

 1      1     .       0       1
 2      1     1       1       0
 3      1     2       1       1
 4      1     3       1       1

Or to get closer to want you said you wanted you could instead put the DAY value into the DAY1 and DAY2 variables.

data want;
  merge have(in=in1) have2(in=in2);
  by id day;
  if in1 then day1=day;
  if in2 then day2=day;
run;

Result

Obs    ID    Day    day1    day2

 1      1     .       .       .
 2      1     1       1       .
 3      1     2       2       2
 4      1     3       3       3

[terjeph]

The first obs. in the second dataset is a missing. I'd like to keep the missing.

It seems like this solution is working as well. Thanks.