DATA Step, Macro, Functions and more

How can I combine multiple rows to a single row?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

How can I combine multiple rows to a single row?

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            270      350

1  2000   20      300     320

1  2001            122      500


Accepted Solutions
Solution
‎02-27-2012 04:04 PM
Respected Advisor
Posts: 4,920

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

There is this wicked :smileycool: technique learned from Tom recently:

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;

proc print; run;

Works just fine. - PG

PG

View solution in original post


All Replies
Solution
‎02-27-2012 04:04 PM
Respected Advisor
Posts: 4,920

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

There is this wicked :smileycool: technique learned from Tom recently:

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;

proc print; run;

Works just fine. - PG

PG
Super Contributor
Posts: 1,636

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

another approach:

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

;

proc sql;

  create table want as

    select distinct id,year,sum(value1) as value1,sum(value2) as value2,sum(value3) as value3

            from have

              group by id,year;

quit;

proc print; run;

                 Obs    ID    Year    value1    value2    value3

                   1      1    1999       .             270       350

                   2      1    2000      20          300       320

                   3      1    2001       .            122       500

Linlin

Super User
Posts: 11,343

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

And another if the data always matches the shown examples;

proc summary data=have nway;

class id year;

var value1 value2 value3;

output out=have (drop=_type_ _freq) max=;

run;

Respected Advisor
Posts: 3,156

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

OK, now here goes the non-wicked data step approach:

data want (drop=i _Smiley Happy;

array tmp(3) _t1-_t3 ;

do _n_=1 by 1 until (last.year);

    set have;

    by id year;

    array val(*) value1-value3;

      do i=1 to dim(val);

         val(i)=ifn(missing(val(i)),tmp(i),val(i));

           tmp(i)=val(i);

       end;

end;

run;

Regards,

Haikuo

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 2949 views
  • 10 likes
  • 5 in conversation