03-13-2012 09:53 AM
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 ?
03-13-2012 10:01 AM
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'.
03-13-2012 10:05 AM
I wanted to see the results using Inner Join only and i need to use Inner Join along with SET Statement. Is it possible ?
03-13-2012 10:47 AM
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);
if in1 and in2;
03-13-2012 10:07 AM
proc sort data=dataset1; by rollnumber; run;
proc sort data=dataset2; by rollnumber; run;
there will be only 5 observations in the final dataset.
03-13-2012 10:26 AM
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:
input 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
input Rollnumber $ Dob :$10. 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
do i=1 to nobs until (eof);
set h2 (rename=rollnumber=r) end=eof nobs=nobs point=i;
if rollnumber=r then output;