## Concatenate multiple rows into a single value

Solved
Occasional Learner
Posts: 1

# Concatenate multiple rows into a single value

I have a data set that where I need to combnine several rows into one.  See below

ID       Date       Var1        var2      var3

1          7/4/17      1             .            .

1          7/4/17      .             0            .

1          7/4/17      .             .            1

1          5/4/17      0             .            .

1          5/4/17      .             0            .

1          5/4/17      .             .             0

2          6/4/17      .             1            .

2          6/4/17      0             .            .

2          6/4/17      .              .           1

3          7/14/17      0             .            .

3          7/14/17      .             0            .

3         7/14/17       .            .             1

The desired outcome would be a single row per ID.  Note - There are some ID that have data from an additional date.  I need both.

ID       Date        Var1         Var2        Var3

1         7/4/17        1              0              1

1         5/4/17        0              0              0

2         6/4/17        0              1              1

3         7/14/17      0              0              1

Thank you for any help and input

Accepted Solutions
Solution
‎07-11-2017 09:13 AM
Super User
Posts: 5,499

## Re: Concatenate multiple rows into a single value

One way:

proc sort data=have;

by id date;

run;

data want;

update have (obs=0) have;

by id date;

run;

All Replies
Solution
‎07-11-2017 09:13 AM
Super User
Posts: 5,499

## Re: Concatenate multiple rows into a single value

One way:

proc sort data=have;

by id date;

run;

data want;

update have (obs=0) have;

by id date;

run;

PROC Star
Posts: 325

## Re: Concatenate multiple rows into a single value

proc sql;

select  ID,       Date,       max(Var1) as var1,         max(Var2) as Var2,        max(Var3) as Var3

from have

group by ID, date;

quit;

Super Contributor
Posts: 271

## Re: Concatenate multiple rows into a single value

``````data have;
input id date \$ Var1 Var2 Var3;
cards;
1          7/4/17      1             .            .
1          7/4/17      .             0            .
1          7/4/17      .             .            1
1          5/4/17      0             .            .
1          5/4/17      .             0            .
1          5/4/17      .             .            0
2          6/4/17      .             1            .
2          6/4/17      0             .            .
2          6/4/17      .             .            1
3          7/14/17     0             .            .
3          7/14/17     .             0            .
3          7/14/17     .             .            1
;
run;

proc sort data = have;
by id date;
run;

data want;
set have;
by id date;
retain var1_ var2_ var3_;

if first.date then
do;
var1_ = .;
var2_ = .;
var3_ = .;
end;

if not missing (var1) then
var1_ = var1;

if not missing (var2) then
var2_ = var2;

if not missing (var3) then
var3_ = var3;
var1 = var1_;
var2 = var2_;
var3 = var3_;

if last.date then
output;
drop var1_ var2_ var3_;
run;``````
☑ This topic is solved.