topic Re: merge/join data in SAS in New SAS User

merge/join data in SAS

sasprogramming — Thu, 05 Dec 2019 04:26:22 GMT

I have the following two data sets that looks like:

ID	X	Y	Z
4	24	23	29
5	42	31	36
6	50	43	42
7	43	31	21
8	50	44	28
9	42	49	30

and

ID	W
1	60
2	78
3	93
4	43
5	98
6	97
7	83
8	51
9	83
10	42
11	63
12	95
13	46
14	72

I want to merge these for only complete information. I.e. I want to keep all of the ID's for the first data set and create a new column W which comes from the second data set. My desired result looks like this:

ID	X	Y	Z	W
4	24	23	29	43
5	42	31	36	98
6	50	43	42	97
7	43	31	21	83
8	50	44	28	51
9	42	49	30	83

How can I achieve this result?

Re: merge/join data in SAS

Jagadishkatam — Thu, 05 Dec 2019 04:45:31 GMT

please try the below code

proc sort data=tableA;
by id;
run;

proc sort data=tableB;
by id;
run;

data want;
merge tableA(in=a) tableB(in=b);
by id;
if a;
run;

Re: merge/join data in SAS

sasprogramming — Thu, 05 Dec 2019 04:59:11 GMT

That works thanks!

Is this what is called an 'inner join'? If so how would I modify the code for an 'outer join'?

Re: merge/join data in SAS

Kurt_Bremser — Thu, 05 Dec 2019 06:40:21 GMT

Or do

proc sql;
create table want as
select table1.*, table2.w
from table1, table2
where table1.id = table2.id
order by table1.id;
quit;

ID	X	Y	Z	W
4	24	23	29	43
5	42	31	36	98
6	50	43	42	97
7	43	31	21	83
8	50	44	28	51
9	42	49	30	83

ID	X	Y	Z	W
4	24	23	29	43
5	42	31	36	98
6	50	43	42	97
7	43	31	21	83
8	50	44	28	51
9	42	49	30	83

ID	X	Y	Z	W
4	24	23	29	43
5	42	31	36	98
6	50	43	42	97
7	43	31	21	83
8	50	44	28	51
9	42	49	30	83