BookmarkSubscribeRSS Feed
sambasiva_ravuri_tcs_com
Calcite | Level 5

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.

6 REPLIES 6
Haikuo
Onyx | Level 15

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

sambasiva_ravuri_tcs_com
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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;

Linlin
Lapis Lazuli | Level 10

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.

Haikuo
Onyx | Level 15

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

Linlin
Lapis Lazuli | Level 10

100% agree with you.   Thanks - Linlin

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 866 views
  • 0 likes
  • 4 in conversation