Dear All,
Am new to SAS and i have a silly question.
The Two Datasets are as below. I need to Join these 2 datasets by Rollnumber using SET Statement ?
Dataset1 | |||||
Rollnumber | Name | Age | Gender | Mark | Dept |
5 | don | 23 | M | 45 | Science |
4 | andrew | 45 | M | 33 | Maths |
1 | michael | 12 | F | 29 | Physics |
2 | steve | 44 | F | 54 | Chemistry |
3 | mark | 23 | M | 17 | Maths |
Dataset2 | |||||
Roll number | Dob | Salary | experience | ||
1 | 11/12/87 | 2500 | 2 | ||
5 | 12/04/56 | 3400 | 3 | ||
3 | 03/06/79 | 1200 | 4 | ||
2 | 04/05/94 | 44560 | 4 | ||
4 | 03/07/99 | 12908 | 3 | ||
6 | 03/07/99 | 12908 | 7 |
Regards.
S Ravuri.
We need to know what kind of join you are aiming: an intersection (inner join) or outer join (left, right or full). So you will have up to 4 different results depending on what you want.
Sorry I had to borrow the SQL terms, when you said you want to use 'set'.
Regards,
Haikuo
I wanted to see the results using Inner Join only and i need to use Inner Join along with SET Statement. Is it possible ?
Thanks,
S Ravuri.
No. You can use the SET statement to interleave the rows. This would be appropriate if the structure of the two files was similar and you just want to append them together (think UNION in SQL). You could use a BY statement to keep the results in proper order.
Instead you want to combine the variables from the two different datasets onto the same observation in the output.
So you want to use the MERGE statement.
You can use the IN= dataset options to allow you to decide if you want to keep all cases or only those in both datasets (or one particular dataset).
data want ;
merge dataset1 (in=in1) dataset2 (in=in2);
by rollnumber;
if in1 and in2;
run;
proc sort data=dataset1; by rollnumber; run;
proc sort data=dataset2; by rollnumber; run;
data want;
set dataset1;
set dataset2;
run;
there will be only 5 observations in the final dataset.
Linlin,
Your solution only works when 'rollnumber' in two dataset are paired up exactly the same, no jump in numbers.
First of all, I would say this is NOT a job for 'set', it is a 'merge' job. But you could do it by brutal force like this:
data h1;
input Rollnumber $ Name $ Age Gender $ Mark Dept $;
cards;
5 don 23 M 45 Science
4 andrew 45 M 33 Maths
1 michael 12 F 29 Physics
2 steve 44 F 54 Chemistry
;
data h2;
input Rollnumber $ Dob :$10. Salary experience;
cards;
1 11/12/87 2500 2
5 12/04/56 3400 3
3 03/06/79 1200 4
2 04/05/94 44560 4
4 03/07/99 12908 3
6 03/07/99 12908 7
;
data want;
set h1;
do i=1 to nobs until (eof);
set h2 (rename=rollnumber=r) end=eof nobs=nobs point=i;
if rollnumber=r then output;
end;
run;
proc print;run;
Regards,
Haikuo
100% agree with you. Thanks - Linlin
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.