topic Re: Combine multiple rows to a single row in SAS Programming

Combine multiple rows to a single row

Timbim — Mon, 30 Apr 2018 04:48:07 GMT

Hello,

I'm hoping someone could help me with the following.

I have the following output table and would like the data to be in one row instead of multiple rows.

A	B	C	D	E
28846512	.	.	.	.
.	10942982	.	.	.
.	.	1418164	.	.
.	.	.	207.29	.
.	.	.	.	20092412

I want the above table to look like below:

A	B	C	D	E
28846512	10942982	1418164	207.29	20092412

Value your help.

Kind regards,

Mags

Re: Combine multiple rows to a single row

Shmuel — Mon, 30 Apr 2018 05:28:20 GMT

What type of input you have ? is it a sas dataset or external file (like: .txt or .xsl) ?

Is there an ID column ? How many variables are in a row ?

What do you want to do with the data ?

Why not save it all in one column ?

The solution depends on your answers.

Re: Combine multiple rows to a single row

Timbim — Mon, 30 Apr 2018 05:30:47 GMT

Hi Shmuel,

It is a sas dataset and does not have a column ID.

Thank you .

Re: Combine multiple rows to a single row

PGStats — Mon, 30 Apr 2018 05:32:26 GMT

proc means data=have noprint;
var _all_;
output out=want(drop=_: ) max=;
run;

Re: Combine multiple rows to a single row

Shmuel — Mon, 30 Apr 2018 05:36:36 GMT

Assuming there are 5 columns:

data want;
  set have;
        retain v1-v5;
        array  vx v1-v5;
        array inp a b c d e;
        i = mod(_N_, 5);
        vx(i) - inp(i);
       
       if i=0 then output;
      keep v1-v5;
run;

Re: Combine multiple rows to a single row

Timbim — Mon, 30 Apr 2018 05:40:24 GMT

Thank you so much PG. It works perfectly.

Re: Combine multiple rows to a single row

Timbim — Mon, 30 Apr 2018 05:41:45 GMT

Thanks kindly Shmuel.

Re: Combine multiple rows to a single row

euro_andres — Mon, 30 Apr 2018 07:19:30 GMT

Beautiful solution!