topic Can i join two datasets by Rollnumber using SET Statement ? in SAS Programming

Can i join two datasets by Rollnumber using SET Statement ?

sambasiva_ravuri_tcs_com — Tue, 13 Mar 2012 13:53:44 GMT

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.

Can i join two datasets by Rollnumber using SET Statement ?

Haikuo — Tue, 13 Mar 2012 14:01:31 GMT

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

Can i join two datasets by Rollnumber using SET Statement ?

sambasiva_ravuri_tcs_com — Tue, 13 Mar 2012 14:05:15 GMT

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.

Can i join two datasets by Rollnumber using SET Statement ?

Linlin — Tue, 13 Mar 2012 14:07:18 GMT

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.

Can i join two datasets by Rollnumber using SET Statement ?

Haikuo — Tue, 13 Mar 2012 14:26:11 GMT

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

Can i join two datasets by Rollnumber using SET Statement ?

Linlin — Tue, 13 Mar 2012 14:31:59 GMT

100% agree with you. Thanks - Linlin

Can i join two datasets by Rollnumber using SET Statement ?

Tom — Tue, 13 Mar 2012 14:47:45 GMT

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;