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
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
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.