topic Re: Concatenate multiple rows into a single value in SAS Programming

Concatenate multiple rows into a single value

Jadonnell — Wed, 05 Jul 2017 16:28:11 GMT

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

Re: Concatenate multiple rows into a single value

Astounding — Wed, 05 Jul 2017 18:09:17 GMT

One way:

proc sort data=have;

by id date;

run;

data want;

update have (obs=0) have;

by id date;

run;

Re: Concatenate multiple rows into a single value

kiranv_ — Wed, 05 Jul 2017 19:52:48 GMT

proc sql;

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

from have

group by ID, date;

quit;

Re: Concatenate multiple rows into a single value

SAS_inquisitive — Wed, 05 Jul 2017 20:12:16 GMT

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;