topic Re: tricky merging in New SAS User

tricky merging

chriswong1386 — Tue, 30 Nov 2021 01:47:00 GMT

data test1;
input id $ marks1 ;
datalines;
a1 10
a2 20
a3 30
a4 40
a7 70
a8 80
a9 90
a10 100
;
data test2;
input id $ marks2;
datalines;
a1 10
a2 20
a3 30
a4 40
a5 50
a6 60
a7 70
a8 80
a9 90
a10 100
;

proc sort data=test1;
by id;
proc sort data=test2;
by id;

data merge1;
merge test1 test2;
by id;

the current result is the following

a10

100

But I just want to show the output contain in test two but missing in test one by id

the following is the result I desire:

a5 50

a6 60

please help! I can't find similar examples in my lecture notes ,wanna cry

Re: tricky merging

chriswong1386 — Tue, 30 Nov 2021 01:48:14 GMT

need to be done by a data set ,can not use proc sql

Re: tricky merging

mkeintz — Tue, 30 Nov 2021 02:05:09 GMT

Look at

The IN= Data Set Option
and
The Subsetting IF statement.

Re: tricky merging

Tom — Tue, 30 Nov 2021 05:38:14 GMT

Do you want the observations that are not in TEST1?

data merge1;
  merge test1(in=in1) test2(in=in2);
  by id;
  if not in1;
run;

Or the observations that have a missing value for the variable MARKS1?

data merge1;
  merge test1(in=in1) test2(in=in2);
  by id;
  if missing(marks1);
run;

Re: tricky merging

AndreaVianello — Tue, 30 Nov 2021 09:50:08 GMT

data test1;
input id $ marks1 ;
datalines;
a1 10
a2 20
a3 30
a4 40
a7 70
a8 80
a9 90
a10 100
;
data test2;
input id $ marks2;
datalines;
a1 10
a2 20
a3 30
a4 40
a5 50
a6 60
a7 70
a8 80
a9 90
a10 100
;
run;

proc sql;
create table want as
select test1.id, marks1, marks2
from test1 right join test2
on test1.id=test2.id
; quit;