Proc sql to create combined table

[forumsguy]

Hi there,

I am new to SAS. I would like to seek help from all of you in order to create a combined table which can be later used to produce report using proc report, Wellm here it goes.

My first table is

Table1

data1 data2 data3     position

102        204       305 1

403        507       609 4

707        806       904 3

Table2

data4  data5  data6 position

1100  1200    1600  1

1700  1900    200    2

4000  7000    8000  5

Here the position variable is a reference variable which would determine my final tables position like if its 1 then that obs will be my first record m 4th then it will be my 4th record n so on

So my output should look like

Output

data1   data2   data3  data4   data5  data6

102       204      305      1100    1200    1600

.               .         .             1700    1900     200

707       806      904       .           .           .

403       507      609      .             .             .

.             .             .           4000    7000     8000

Is there any way to do this purely in Proc SQL or do we have to take use of data steps ????  Thanks a lot in advance

[Linlin · Accepted solution]

Yes, you can use full join to get what you want.

data one;

input data1 data2 data3 position;

cards;

102        204       305 1

403        507       609 4

707        806       904 3

;

data two;

input data4  data5  data6 position;

cards;

1100  1200    1600  1

1700  1900    200   2

4000  7000    8000  5

;

proc sql;

  create table want as select data1,data2,data3,data4,data5,data6

     from one

             full join two

              on one.position=two.position;

           quit;

proc print;run;

                Obs    data1    data2    data3    data4    data5    data6

                     1      102      204      305      1100     1200     1600

                     2        .        .        .                  1700     1900      200

                     3      707      806      904         .        .        .

                     4      403      507      609         .        .        .

                     5        .        .        .                 4000     7000     8000

[Ksharp]

Another way is to use merge statement.

data one;
input data1 data2 data3 position;
cards;
102        204       305 1
403        507       609 4
707        806       904 3
;
run;

data two;
input data4  data5  data6 position;
cards;
1100  1200    1600  1
1700  1900    200   2
4000  7000    8000  5
;
run;
proc sort data=one;by position;run;
proc sort data=two;by position;run;
data want;
 merge one two;
 by position;
 drop position;
run;

Ksharp