DATA Step, Macro, Functions and more

Can i join two datasets by Rollnumber using SET Statement ?

Reply
Contributor
Posts: 42

Can i join two datasets by Rollnumber using SET Statement ?

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




RollnumberNameAgeGenderMarkDept
5don23M45Science
4andrew45M33Maths
1michael12F29Physics
2steve44F54Chemistry
3mark23M17Maths












Dataset2




Roll numberDobSalaryexperience

111/12/8725002

512/04/5634003

303/06/7912004

204/05/94445604

403/07/99129083

603/07/99129087

Regards.

S Ravuri.

Respected Advisor
Posts: 3,124

Can i join two datasets by Rollnumber using SET Statement ?

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

Contributor
Posts: 42

Can i join two datasets by Rollnumber using SET Statement ?

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.

Super User
Super User
Posts: 6,499

Can i join two datasets by Rollnumber using SET Statement ?

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;

Super Contributor
Posts: 1,636

Can i join two datasets by Rollnumber using SET Statement ?

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.

Respected Advisor
Posts: 3,124

Can i join two datasets by Rollnumber using SET Statement ?

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

Super Contributor
Posts: 1,636

Can i join two datasets by Rollnumber using SET Statement ?

100% agree with you.   Thanks - Linlin

Ask a Question
Discussion stats
  • 6 replies
  • 291 views
  • 0 likes
  • 4 in conversation