Moving multiple rows into a single row

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Moving multiple rows into a single row

Hi all,

I have a dataset simplified like this:

1  1  1  1  1 

2  2  2  2  2

3  3  3  3  3

4  4  4  4  4

I would like to convert to a data set like this into a single row:

1  1  1  1  1  2  2  2  2  2  3  3  3  3  3  4  4  4  4  4

Can someone help with a SAS code that could do this, as I need to do this with a much larger dataset?

Thanks

Daniel


Accepted Solutions
Solution
‎04-13-2014 12:02 PM
Super User
Super User
Posts: 6,497

Re: Moving multiple rows into a single row

You need to know how many rows and columns you have (that is easy to figure out).

data have ;

  input x1-x5 ;

cards;

1  1 1  1  1

2  2 2  2  2

3  3 3  3  3

4  4 4  4  4

run;

%let nrows=4 ;

%let ncols=5 ;

data want ;

  do _n_=1 to nobs ;

    set have end=eof nobs=nobs;

    array old _all_ ;

    array new(&nrows,&ncols) ;

    do _i_=1 to dim(old);

      new(_n_,_i_)=old(_i_);

    end;

  end;

  output;

  stop;

  keep new: ;

run;

View solution in original post


All Replies
PROC Star
Posts: 7,356

Re: Moving multiple rows into a single row

Do all of the records from the larger database have to be moved onto just one row and, if not, what determines which ones should go on the same row?

Occasional Contributor
Posts: 6

Re: Moving multiple rows into a single row

Yes, all the rows from the large dataset (table) have to be moved into just one row. Thus the final table should have just one row and multiple columns.

Super User
Posts: 9,662

Re: Moving multiple rows into a single row

As Arthur said. if there is another group variable ? since you said you have a large table.

Your large table has only one variable or many variables ?

Message was edited by: xia keshan

PROC Star
Posts: 7,356

Re: Moving multiple rows into a single row

Why do you want to move all of the data to one wide record?

Occasional Contributor
Posts: 6

Re: Moving multiple rows into a single row

To allow the assembling of these (wide record) single rows from multiple subjects for cluster analysis

Super User
Posts: 9,662

Re: Moving multiple rows into a single row

proc transpose is easy way if your table is not too large .

Occasional Contributor
Posts: 6

Re: Moving multiple rows into a single row

It is quite large actually...

PROC Star
Posts: 7,356

Re: Moving multiple rows into a single row

Your data doesn't have to be on one row to do a cluster analysis.

Occasional Contributor
Posts: 6

Re: Moving multiple rows into a single row

The table includes data from just one subject, which I want to move to a single row. Multiple such tables exist for the other subjects. If I'm able to put them all on the same row in their individual tables, I can create a new table where I assemble all these single rows for a cluster analysis.

I don't know if there's another way to do this, thus my question.

PROC Star
Posts: 7,356

Re: Moving multiple rows into a single row

How many variables per subject?  And, how many subjects?

It sounds like you would be best off combining all of the data into one table, ensuring that subject is identified in each record, and THEN using proc transpose twice (once to make the wide file long, then another to make the desired wide file).

Solution
‎04-13-2014 12:02 PM
Super User
Super User
Posts: 6,497

Re: Moving multiple rows into a single row

You need to know how many rows and columns you have (that is easy to figure out).

data have ;

  input x1-x5 ;

cards;

1  1 1  1  1

2  2 2  2  2

3  3 3  3  3

4  4 4  4  4

run;

%let nrows=4 ;

%let ncols=5 ;

data want ;

  do _n_=1 to nobs ;

    set have end=eof nobs=nobs;

    array old _all_ ;

    array new(&nrows,&ncols) ;

    do _i_=1 to dim(old);

      new(_n_,_i_)=old(_i_);

    end;

  end;

  output;

  stop;

  keep new: ;

run;

Occasional Contributor
Posts: 6

Re: Moving multiple rows into a single row

Awesome! This worked, thanks!!

☑ This topic is SOLVED.

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

Discussion stats
  • 12 replies
  • 489 views
  • 0 likes
  • 4 in conversation