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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.