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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.