topic Re: How can I combine multiple rows to a single row? in SAS Programming

How can I combine multiple rows to a single row?

Roger — Mon, 27 Feb 2012 20:52:18 GMT

Hello,

I have a table that has multiple rows, and some of the values are missing. I just need to combine them into a single row by year.

I have done it with two data sets. In this case, I only have one data set. What is best way to combine them to a single row?

Thanks team in advance!

Input table:

ID Year Value1 Value2 Value3

1 1999 270

1 1999 350

1 1999

1 2000 20

1 2000 300

1 2000 320

1 2001 122

1 2001

1 2001 500

I need the output table as below:

ID Year Value1 Value2 Value3

1 1999 350 270

1 2000 20 300 320

1 2001 122 500

Re: How can I combine multiple rows to a single row?

peterz — Tue, 28 Feb 2012 12:26:09 GMT

I think you are after:

data

input_table;

infile

cards missover dsd;

input

ID Year Value1 Value2 Value3;

cards

;

1,1999,,270

1,1999,,,350

1,1999,,,

1,2000,20

1,2000,,300

1,2000,,,320

1,2001,,122

1,2001,,,

1,2001,,,500

;

proc means data=input_table noprint nway;

class id year;

var value1 value2 value3;

output

out=output_table(drop=_type_ _freq_) max

quit

;

Re: How can I combine multiple rows to a single row?

peterz — Tue, 28 Feb 2012 12:27:34 GMT

Hmmmm, not used to this editor thing. It seems pretty stupid. It has moved all the semi-colons and added carriage returns in random places but the basic syntax you need is there.

It also doesn't like me Ctrl+Ving stuff into it.

Re: How can I combine multiple rows to a single row?

Haikuo — Tue, 28 Feb 2012 13:14:46 GMT

What happened? This thread has been sufficiently discussed and multiple answers have been offered yesterday, now they are all GONE???

Re: How can I combine multiple rows to a single row?

Hima — Tue, 28 Feb 2012 14:25:07 GMT

Yes Hai.Kuo, something happened but they are all gone. Here is the code posted by PGStats yesterday. I saved it.

data have;
input ID Year Value1 Value2 Value3;
datalines;
1 1999      .      270 .
1 1999      . .                 350
1 1999   . . .
1 2000    20 . .
1 2000    .         300 .
1 2000    .          .           320
1 2001     .        122 .
1 2001     .         . .
1 2001     .         .           500
;

data want;
update have(obs=0) have;
by id year;
run;

Re: How can I combine multiple rows to a single row?

art297 — Tue, 28 Feb 2012 14:28:21 GMT

Hima: Glad you found it as I was going to repost it myself, and add in the original statement crediting Tom Abernathy for originally suggesting it.

Apparently some people still don't realize that, if they delete a post, they also delete all posts that have replied to their post.

Re: How can I combine multiple rows to a single row?

Hima — Tue, 28 Feb 2012 14:31:35 GMT

Yes Art. I am totally agreeing with you. PGStats said that the saved the code from Tom. I forgot to add that in my previous post.

Re: How can I combine multiple rows to a single row?

Linlin — Tue, 28 Feb 2012 14:58:39 GMT

The OP post the question twice.

http://communities.sas.com/message/117621#117621

Re: How can I combine multiple rows to a single row?

Haikuo — Tue, 28 Feb 2012 15:41:38 GMT

aha, the 'wicked' update statement application. Just FYI, after I have studied help document on 'update', it turns out in this case, both (obs=0) and (obs=1) would work.

Regards,

Haikuo

Re: How can I combine multiple rows to a single row?

art297 — Tue, 28 Feb 2012 15:49:10 GMT

Yes but, if you are dealing with a file containing millions of IDs, why read all of those records if you don't have to?

Re: How can I combine multiple rows to a single row?

hpk — Wed, 31 Jul 2013 19:18:56 GMT

Hello Arthur,

How can we get this kind of a result?

O/P

1 1999 (22,30,12) (270,40,14) (32,50,14) - Brackets means a single cell.

2 2000 (18,20,15) (22,33,22) (25,36,320)

input ID Year Value1 Value2 Value3;

datalines;

1 1999 22 270 32

1 1999 30 40 50

1 1999 . 12 14

1 2000 18 22 25

1 2000 20 33 36

1 2000 15 22 320

1 2001 . 122 .

1 2001 . . .

1 2001 . . 500

;

data want;
update have(obs=0) have;
by id year;
run;

Thank you!

Re: How can I combine multiple rows to a single row?

AskoLötjönen — Fri, 02 Aug 2013 07:22:25 GMT

Hi Roger,

I think that using of proc Sql is the easiest solution:

data test;

input ID @1 year 4-7 value1 11-17 value2 22-28 value3 33-39;

datalines;

1 1999

;

run;

proc sql;

create table test2 as

select id,

year,

max(value1) as value1,

max(value2) as value2,

max(value3) as value3

from test

group by id, year;

quit;

Re: How can I combine multiple rows to a single row?

UrvishShah — Fri, 02 Aug 2013 12:48:17 GMT

Here is my try...

proc sql;

create table want as

select id,year,

sum(value1) as value1,

sum(value2) as value2,

sum(value3) as value3

from have

group by 1,2;

quit;

-Urvish

Re: How can I combine multiple rows to a single row?

kmw — Fri, 29 Sep 2017 16:04:39 GMT

Editor's note: this topic is very popular. Thanks to @peterz, @Hima, and @AskoLötjönen and others who contributed useful replies. We have consolidated some of these into this single reply so that future readers may benefit.

/* input data */

data input_table;

infile cards missover dsd;

input ID Year Value1 Value2 Value3;

datalines;

1,1999,,270

1,1999,,,350

1,1999,,,

1,2000,20

1,2000,,300

1,2000,,,320

1,2001,,122

1,2001,,,

1,2001,,,500

;

run;　

/* Method #1 using PROC MEANS */

proc means data=input_table noprint nway;

class id year;

var value1 value2 value3;

output

out=output_table(drop=_type_ _freq_) max=;

quit;

/* Method #2 using the UPDATE statement */

data want;

update input_table (obs=0) input_table;

by id year;

run;

/* Method 3 using PROC SQL */

proc sql;

create table test2 as

select id,

year,

max(value1) as value1,

max(value2) as value2,

max(value3) as value3

from input_table

group by id, year;

quit;

/* Resulting data set */

Obs ID Year value1 value2 value3

1 1 1999 . 270 350

2 1 2000 20 300 320

3 1 2001 . 122 500

Re: How can I combine multiple rows to a single row?

El_Aura — Wed, 10 Jan 2018 19:42:13 GMT

This is awesome! Thank you!

Re: How can I combine multiple rows to a single row?

athulyamenon — Mon, 26 Mar 2018 02:55:37 GMT

/* Method #2 using the UPDATE statement */

data want;

update input_table (obs=0) input_table;

by id year;

run;

This is a great piece of code! Thank you for this. I would like to understand how it works though. Thanks again!

Re: How can I combine multiple rows to a single row?

a079011 — Tue, 21 Aug 2018 13:13:59 GMT

I am trying to do something similar except, if value 1 has more then then one value I need the table to include an additional row.

DATA HAVE

Obs id year value1 value2 value3

1 1 1999 - 270 350

2 1 1999 850 - -

3 1 1999 450 - -

4 2 2000 10 20 -

5 2 2000 - - 15

6 3 2001 - 55 -

7 3 2001 - 65 -

8 3 2001 - 75 -

9 4 2002 - - 85

10 4 2002 99 - -

DATA WANT

Obs id year value1 value2 value3

1 1 1999 850 270 350

1 1 1999 450 - -

2 2 2000 10 20 15

3 3 2001 - 55 -

4 3 2001 - 65 -

5 3 2001 - 75 -

6 4 2002 99 - 85