Combining tables by row numbers

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Combining tables by row numbers

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


Accepted Solutions
Solution
‎09-11-2013 09:35 AM
Super User
Super User
Posts: 6,500

Re: Combining tables by row numbers

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         .         .


View solution in original post


All Replies
New Contributor
Posts: 4

Re: Combining tables by row numbers

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?

TimeResultsTimeResults
02550250
62636237
1223612233
1823318234
Solution
‎09-11-2013 09:35 AM
Super User
Super User
Posts: 6,500

Re: Combining tables by row numbers

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         .         .


New Contributor
Posts: 4

Re: Combining tables by row numbers

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

Super Contributor
Posts: 1,636

Re: Combining tables by row numbers

you could merge the two tables without by variables:

data want;

merge table1 table2;

run;

New Contributor
Posts: 4

Re: Combining tables by row numbers

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.

Super Contributor
Posts: 1,636

Re: Combining tables by row numbers

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 275 views
  • 3 likes
  • 3 in conversation