topic Re: Adding variables with matching observations in SAS Programming

Adding variables with matching observations

mcook — Thu, 02 Jul 2020 15:08:12 GMT

I have several tables

Table1:

Var1

Table 2:

Var2

Table 3:

Var3

i want to get these into a single table in the form of

Var1 Var2 Var3

A A

B B

C C

D D D

F F F

G G G

any suggestions?

Re: Adding variables with matching observations

mklangley — Thu, 02 Jul 2020 15:34:36 GMT

proc sql;
    create table want as
    select *
    from table1 a
    full join table2 b on a.var1 = b.var2
    full join table3 c on a.var1 = c.var3
    ;
run;

Re: Adding variables with matching observations

mcook — Thu, 02 Jul 2020 15:39:27 GMT

I have been trying full joins, but it is not working as I expect.

Re: Adding variables with matching observations

mklangley — Thu, 02 Jul 2020 15:49:08 GMT

What do you mean by "it is not working"? Can you include your data and code, and the results you are seeing?

Re: Adding variables with matching observations

andreas_lds — Fri, 03 Jul 2020 07:31:54 GMT

Could be solved with a merge, if you don't mind creating a common key-variable in all dataset:

data Table1;
   input Var1 $;
   key = Var1;

   datalines;
A
B
C
D
E
F
G
;
run;
 

data Table2;
   input Var2 $;
   key = Var2;

   datalines;
A
D
F
G
;
run;
 

data Table3;
   input Var3 $;
   key = Var3;

   datalines;
B 
C
D
F
G
;
run;

data all;
   merge Table1 Table2 Table3;
   by key;

   drop Key;
run;

Re: Adding variables with matching observations

mcook — Mon, 06 Jul 2020 23:52:42 GMT

Thanks. Full join did finally work. I just had a minor syntax error that i had overlooked.