topic Proc sql to create combined table in SAS Programming

Proc sql to create combined table

forumsguy — Sat, 07 Jan 2012 11:03:11 GMT

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

Proc sql to create combined table

Linlin — Sat, 07 Jan 2012 13:16:28 GMT

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

Proc sql to create combined table

Ksharp — Mon, 09 Jan 2012 06:06:55 GMT

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