topic Re: Concatenating with Proc SQL in SAS Programming

Concatenating with Proc SQL

75063 — Thu, 23 Aug 2018 18:51:28 GMT

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!

Re: Concatenating with Proc SQL

novinosrin — Thu, 23 Aug 2018 19:06:43 GMT

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

Re: Concatenating with Proc SQL

75063 — Thu, 23 Aug 2018 19:49:57 GMT

Thank you for your inputs. It worked well for my data sets.