topic Combining tables by row numbers in SAS Programming

Combining tables by row numbers

RichardM — Wed, 11 Sep 2013 13:10:47 GMT

I need to combine two data tables as below: DATA table1; INPUT Time Results; Lines; 0 255 6 263 12 236 18 233 ; RUN; DATA table2; INPUT Time Results_2; Lines; 0 250 6 237 12 233 18 234 ; RUN; The output that I need is as below: Time Results Time Results 0 255 0 250 6 263 6 237 12 236 12 233 18 233 18 234 The usual horizontal concatenation procedures such as MERGE and SET would combine the Time column. If need to simply paste together the tables side-by-side, by row numbers (in general, I can have more than 2 data sets and may not have the same number of rows), how can I do that? Thanks, -Richard

Re: Combining tables by row numbers

RichardM — Wed, 11 Sep 2013 13:13:48 GMT

Sorry, my post scrambled the data format. The output that I need is below. How can I do that if I have two separate tables (each having two columns of Time and Results) and I need to paste them together side-by-side?

Time	Results	Time	Results
0	255	0	250
6	263	6	237
12	236	12	233
18	233	18	234

Re: Combining tables by row numbers

Tom — Wed, 11 Sep 2013 13:35:12 GMT

If you can modify the tables to include an actual rownumber variable then just merge by that.

Otherwise you can work a little harder and get SAS to do it from the original data sets.

Make sure that the variable names are unique.

data table1;

input time1 result1 @@;

cards;

0 255 6 263 12 236 18 233

run;

data table2;

input time2 result2 @@;

cards;

0 250 6 237 18 234

run;

data want1 ;

if (eof1 and eof2) then stop;

if not eof1 then set table1 end=eof1 ;

if not eof2 then set table2 end=eof2 ;

output;

call missing(of _all_);

run;

Obs time1 result1 time2 result2

1 0 255 0 250

2 6 263 6 237

3 12 236 18 234

4 18 233 . .

Re: Combining tables by row numbers

RichardM — Wed, 11 Sep 2013 18:41:04 GMT

Thank you very much Tom! The command using eof worked for my application.

Re: Combining tables by row numbers

Linlin — Wed, 11 Sep 2013 20:03:35 GMT

you could merge the two tables without by variables:

data want;

merge table1 table2;

run;

Re: Combining tables by row numbers

RichardM — Wed, 11 Sep 2013 20:18:18 GMT

Thanks Linlin. I initially tried what you suggested using:

data want;

merge table1 table2;

run;

but without giving distinct column names for Results, it does not work the way I need it. With distinct names, then it does work. Now I know two methods to do this - the usual merge command and the eof commands Tom suggested.

Re: Combining tables by row numbers

Linlin — Wed, 11 Sep 2013 20:43:26 GMT

you need to rename the variables:

data table1;
input time result @@;
cards;
0 255 6 263 12 236 18 233
run;

data table2;
input time result @@;
cards;
0 250 6 237 18 234
run;
data want;
merge table1(rename=(time=time1 result=result1)) table2(rename=(time=time2 result=result2)) ;
run;
proc print;run;