topic Re: Merging data sets in SAS Programming

Merging data sets

Amanda_Lemon — Thu, 12 Sep 2019 03:58:42 GMT

Hello,

I am trying to merge two data sets and so far unsuccessfully... I would appreciate any help!

Have: Data set #1 (students are nested within classes, which are in turn nested within teachers)

StudentID ClassID TeacherID

1 1 1
2 1 1
3 2 1
4 2 1
5 3 2
6 4 2
7 4 2
8 4 2

Data set #2: For this data set, 1 class per teacher was randomly selected from Data set #1, and the procedure was repeated 3 times.

ClassID TeacherID Replicate

1 1 1
1 1 2
2 1 3
3 2 1
4 2 2
4 2 3

Want: To add student data to Data set #2. So I need to repeat each row as many times as there are students in class, and add students' IDs.

StudentID ClassID TeacherID Replicate

1 1 1 1
2 1 1 1
1 1 1 2
2 1 1 2
3 2 1 3
4 2 1 3
5 3 2 1
6 4 2 2
7 4 2 2
8 4 2 2
6 4 2 3
7 4 2 3
8 4 2 3

Re: Merging data sets

Reeza — Thu, 12 Sep 2019 04:02:23 GMT

A standard merge didn't work? Can you show what you tried please?

Assuming you merge by classid and teacherID it should work.

Re: Merging data sets

Amanda_Lemon — Thu, 12 Sep 2019 04:10:36 GMT

Nope...

Here is what I tried:

data new;
input Stu_ID ClassID TeacherID;
datalines;
1 1 1
2 1 1
3 2 1
4 2 1
5 3 2
6 4 2
7 4 2
8 4 2
;

data resampled;
input ClassID TeacherID Replicate;
datalines; 
1 1 1
1 1 2
2 1 3
3 2 1
4 2 2
4 2 3
;


proc sort data = new;
by ClassID TeacherID;
run;
proc sort data = resampled;
by ClassID TeacherID;
run;

data merged;
merge new resampled;
by ClassID TeacherID;
run;

proc print data = merged;
run;

Here is what I got:

Obs	Stu_ID	ClassID	TeacherID	Replicate
1	1	1	1	1
2	2	1	1	2
3	3	2	1	3
4	4	2	1	3
5	5	3	2	1
6	6	4	2	2
7	7	4	2	3
8	8	4	2	3

Re: Merging data sets

Reeza — Thu, 12 Sep 2019 04:20:30 GMT

Is that output exactly what you want? Or an example of it?

proc sql;
create table want as
select r.*, n.Stu_id
from resampled as r
cross join new as n
;
quit;

Re: Merging data sets

Amanda_Lemon — Thu, 12 Sep 2019 04:43:21 GMT

Your code doesn't produce what I need... The output in the starting post is exactly want I need, yes.

Your code produced this output:

Obs	ClassID	TeacherID	Replicate	Stu_ID
1	1	1	1	1
2	1	1	2	1
3	2	1	3	1
4	3	2	1	1
5	4	2	2	1
6	4	2	3	1
7	1	1	1	2
8	1	1	2	2
9	2	1	3	2
10	3	2	1	2
11	4	2	2	2
12	4	2	3	2
13	1	1	1	3
14	1	1	2	3
15	2	1	3	3
16	3	2	1	3
17	4	2	2	3
18	4	2	3	3
19	1	1	1	4
20	1	1	2	4
21	2	1	3	4
22	3	2	1	4
23	4	2	2	4
24	4	2	3	4
25	1	1	1	5
26	1	1	2	5
27	2	1	3	5
28	3	2	1	5
29	4	2	2	5
30	4	2	3	5
31	1	1	1	6
32	1	1	2	6
33	2	1	3	6
34	3	2	1	6
35	4	2	2	6
36	4	2	3	6
37	1	1	1	7
38	1	1	2	7
39	2	1	3	7
40	3	2	1	7
41	4	2	2	7
42	4	2	3	7
43	1	1	1	8
44	1	1	2	8
45	2	1	3	8
46	3	2	1	8
47	4	2	2	8
48	4	2	3	8

But what I need is to add StudentID to each case (line) in Data set #2. So, if, say, a class had 3 students, then instead of one line from Data set #2 there will be three, and they will differ only in StudentID.

Thank you so much for helping!

Re: Merging data sets

Reeza — Thu, 12 Sep 2019 05:13:43 GMT

I think this works

data new;
input Stu_ID ClassID TeacherID;
datalines;
1 1 1
2 1 1
3 2 1
4 2 1
5 3 2
6 4 2
7 4 2
8 4 2
;

data resampled;
input ClassID TeacherID Replicate;
datalines; 
1 1 1
1 1 2
2 1 3
3 2 1
4 2 2
4 2 3
;


proc sql;
create table want as
select r.*, n.Stu_id
from resampled as r
full join new as n
on r.classId=n.classId
and r.teacherID=n.teacherID
order by 4, 1, 2, 3
;
quit;

Re: Merging data sets

Amanda_Lemon — Thu, 12 Sep 2019 05:22:57 GMT

Yes! It works! Thank you!!!

Just one question to make sure I understand the code -- what do these mean?

n.
r.
order by 4, 1, 2, 3

Re: Merging data sets

Reeza — Thu, 12 Sep 2019 05:27:04 GMT

n/r are table alias so you don't have to type out the whole table name. This allow you to reference columns from a specific data set, ie n.classID refers to the classID from the new table.

If you don't do that you need to type out the full name, new.table

"ORDER BY" sorts the data, 4, 1, 2, 3 refer to the columns in the data set but I was too lazy to type them out.

I usually never use that except on here or for testing to be honest, I prefer to name the columns for sorting but it's late and I'm lazy 🙂

Re: Merging data sets

Amanda_Lemon — Thu, 12 Sep 2019 05:31:43 GMT

I see! Thank you very much!!