Good morning,
I have 2 data sets set like this
id name age sex
Data set 1:
1 sima 23 f
2 shyam 34 m
3 ana 35 f
4 jacob 26 m
5 chris 34 m
data set 2:
id
1
3
5
how can i have output like this?
1 sima 23 f
3 ana 35 f
5 chris 34 m
There are several ways.
Data step merge would require sorting the data on the matching variable (id) and use of a BY statement.
proc sort data=set1; by id; run; proc sort data=set2; by id; run; /* merge*/ data want; merge set1 (in=in1) set2 (in=in2) ; by id; if in1 and in2; run;
The IN= data set option creates temporary variables that indicate if the current record has values from that data set, 1 when true and 0 when false.
So if both are true you have the matching values.
Or proc sql:
proc sql; create table want as select b.* from set2 as a left join set1 as b on a.id=b.id ; quit;
If you have repeats of the ID in one or both data sets then more consideration may be needed.
I used the first code you sen to me; the log says this;
NOTE: There were 9346 observations read from the data set WORK.CHECKING_A.
NOTE: There were 2576 observations read from the data set WORK.CHRIS_CHECKING_A.
NOTE: The data set WORK.WANT has 0 observations and 58 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
And the result is only name of the variables with no values
proc sql; create table want as select * from set1
where id in (select id from set2) ; 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!
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.