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
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;
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;
Thanks! SQL worked well.
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
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.
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.