DATA Step, Macro, Functions and more

Concatenate multiple rows into a single value

Accepted Solution Solved
Reply
Occasional Learner
Posts: 1
Accepted Solution

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,083

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;

View solution in original post


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

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: 253

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;

Regular Contributor
Posts: 234

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.

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

Discussion stats
  • 3 replies
  • 112 views
  • 2 likes
  • 4 in conversation