i am trying to concatenate two data frames that have repeated observations. The two data frames are have1 and have 2 as shown below
data have1;
input stu $ Freq16;
datalines;
A 2
B 3
C 4
D 5
E 6
F 7
Grand 27
A 3
B 4
G 10
Grand 17
;
run;
data have2;
input stu $ Freq17;
datalines;
A 1
B 5
C 7
D 3
E 2
F 1
Grand 19
A 22
B 7
G 1
Grand 30
;
run;
I am trying to merge the two data frames by the "stu" variable by using the following code -
Proc Sql;
select a.stu, a.Freq16, b.Freq17
from have1 as a inner join have2 as b
on a.stu=b.stu;
Quit;
The output that I am getting is
stu Freq16 Freq17
A 2 1
A 2 22
B 3 5
B 3 7
C 4 7
D 5 3
E 6 2
F 7 1
Grand 27 19
Grand 27 30
A 3 1
A 3 22
B 4 5
B 4 7
G 10 1
Grand 17 19
Grand 17 30
However , my desired output is -
stu Freq16 Freq17
A 2 1
B 3 5
C 4 7
D 5 3
E 6 2
F 7 1
Grand 27 19
A 3 22
B 4 7
G 10 1
Grand 17 30
Can you please advice the correct Proc SQL step. Or is there a better option with the Data Step .
Thank You!
I think data step merge(one to one) or hash, for your sample,
data have1;
input stu $ Freq16;
datalines;
A 2
B 3
C 4
D 5
E 6
F 7
Grand 27
A 3
B 4
G 10
Grand 17
;
run;
data have2;
input stu $ Freq17;
datalines;
A 1
B 5
C 7
D 3
E 2
F 1
Grand 19
A 22
B 7
G 1
Grand 30
;
run;
data want;
merge have1 have2;
run;
Note:
I assume the sample is a good representative of your real meaning the values are exactly in the order that you posted. I didn't merge using by group for the reason the sample doesn't seem to mandate that. Should you use sorted by group merge, the order will change
I think data step merge(one to one) or hash, for your sample,
data have1;
input stu $ Freq16;
datalines;
A 2
B 3
C 4
D 5
E 6
F 7
Grand 27
A 3
B 4
G 10
Grand 17
;
run;
data have2;
input stu $ Freq17;
datalines;
A 1
B 5
C 7
D 3
E 2
F 1
Grand 19
A 22
B 7
G 1
Grand 30
;
run;
data want;
merge have1 have2;
run;
Note:
I assume the sample is a good representative of your real meaning the values are exactly in the order that you posted. I didn't merge using by group for the reason the sample doesn't seem to mandate that. Should you use sorted by group merge, the order will change
Thank you for your inputs. It worked well for my data sets.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.