topic Re: Merging: How di I keep by-variables from both files in SAS Programming

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

terjeph — Thu, 23 May 2024 18:53:11 GMT

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

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

Kurt_Bremser — Thu, 23 May 2024 19:11:58 GMT

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;

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

terjeph — Thu, 23 May 2024 20:13:51 GMT

Thanks! SQL worked well.

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

Tom — Thu, 23 May 2024 21:03:22 GMT

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

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

terjeph — Fri, 24 May 2024 05:48:05 GMT

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.