DATA Step, Macro, Functions and more

How can I convert a table with one row to a table with multiple rows?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How can I convert a table with one row to a table with multiple rows?

Can I convert the data in a table with one row to a table with multiple rows? What are their coding?

 

Original table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

 

Table that I want:

1    2   3   4

5    6   7   8

9   10 11 12

13 14 15 16

 


DSC_0834.JPG

Accepted Solutions
Solution
‎04-13-2017 01:06 PM
Super User
Posts: 19,851

Re: How can I convert a table with one row to a table with multiple rows?

[ Edited ]
Posted in reply to Jonathanzz

In this case an Array is probably your best bet, with an explicit OUTPUT statement. Here's a quick example with two loops.

 

data want;
set have;

array vals(*) val1-val16;
array new(*) new1-new4;

do i=1 to 4;

    do j=1 to 4;
         new(j) = vals(j*i);
    end;

output;

end;
run;

 


Jonathanzz wrote:

Can I convert the data in a table with one row to a table with multiple rows? What are their coding?

 

Original table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

 

Table that I want:

1    2   3   4

5    6   7   8

9   10 11 12

13 14 15 16

 


 

View solution in original post


All Replies
PROC Star
Posts: 7,487

Re: How can I convert a table with one row to a table with multiple rows?

Posted in reply to Jonathanzz
data have;
  input var1-var4 @@;
  cards;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
;

Art, CEO, AnalystFinder.com

 

Solution
‎04-13-2017 01:06 PM
Super User
Posts: 19,851

Re: How can I convert a table with one row to a table with multiple rows?

[ Edited ]
Posted in reply to Jonathanzz

In this case an Array is probably your best bet, with an explicit OUTPUT statement. Here's a quick example with two loops.

 

data want;
set have;

array vals(*) val1-val16;
array new(*) new1-new4;

do i=1 to 4;

    do j=1 to 4;
         new(j) = vals(j*i);
    end;

output;

end;
run;

 


Jonathanzz wrote:

Can I convert the data in a table with one row to a table with multiple rows? What are their coding?

 

Original table:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

 

Table that I want:

1    2   3   4

5    6   7   8

9   10 11 12

13 14 15 16

 


 

Occasional Contributor
Posts: 7

Re: How can I convert a table with one row to a table with multiple rows?

Thanks a lot!
Super Contributor
Posts: 441

Re: How can I convert a table with one row to a table with multiple rows?

I guess that one way for this approach would be:

 

data want (keep = new1-new4);
set have;
array vals(*) var1-var16;
array new(*) new1-new4;

do j = 1 to floor(dim(vals)/4);
  do i = 1 to 4;
    new(i) = vals((j-1)*4 + i);
  end;
  output;
end;
run;
Super User
Posts: 10,041

Re: How can I convert a table with one row to a table with multiple rows?

Posted in reply to Jonathanzz

It is very easy for IML code.

 

data have;
  input var1-var4 @@;
  cards;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
;

proc iml;
use have;
read all var _all_ into x;
close;

want=shape(x,0,4);

create want from want;
append from want;
close;
quit;
Occasional Contributor
Posts: 9

Re: How can I convert a table with one row to a table with multiple rows?

[ Edited ]
Posted in reply to Jonathanzz

I would probably

 

1. add a new observation with values like 1,2,3,4,1,2,3,4,1,2,3,4,....

2. use Proc Transpose

 

When your dataset is large, this can be super efficiant by avoiding loops.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 169 views
  • 6 likes
  • 6 in conversation