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
1 | a1 | 10 | 10 |
2 | a10 | 100 | 100 |
3 | a2 | 20 | 20 |
4 | a3 | 30 | 30 |
5 | a4 | 40 | 40 |
6 | a5 | . | 50 |
7 | a6 | . | 60 |
8 | a7 | 70 | 70 |
9 | a8 | 80 | 80 |
10 | a9 | 90 | 90 |
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
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;
Look at
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;
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.