## "Concatenate" observations by group

Solved
Occasional Contributor
Posts: 16

# "Concatenate" observations by group

Hi,

I have a data like this

hospital  X       Y       Z

1          a          X1      Y1    Z1

2          a          X2      Y2    Z2

3          a          X3     Y3     Z3

4          b          X4     Y4     Z4

5          b          X5     Y5     Z5

How do I make it like this

hospital     X     Y     Z      X'     Y'     Z'     X''     Y''     Z''

1          a             X1   Y1   Z1    X2   Y2    Z2    X3     Y3     Z3

2          b             X4   Y4   Z4    X5   Y5    Z5     .     .          .

Thank you

Accepted Solutions
Solution
‎01-15-2015 05:04 PM
Posts: 5,540

## Re: "Concatenate" observations by group

Another way :

data have;

length hospital X Y Z \$8;

input obs hospital  X Y Z;

datalines;

1          a          X1     Y1    Z1

2          a          X2     Y2    Z2

3          a          X3     Y3    Z3

4          b          X4     Y4    Z4

5          b          X5     Y5    Z5

;

data temp / view=temp;

length id idlabel \$32;

do i = 1 by 1 until(last.hospital);

set have; by hospital;

array t{*} X--Z;

do j = 1 to dim(t);

id = catx("_", vname(t{j}), i);

idlabel = cats(vname(t{j}), substrn(repeat("'", i),3));

var = t{j};

output;

end;

end;

keep hospital id idlabel var;

run;

proc transpose data=temp out=want(drop=_;

by hospital;

id id;

idlabel idlabel;

var var;

run;

proc print data=want label noobs; run;

PG

PG

All Replies
Super User
Posts: 6,785

## Re: "Concatenate" observations by group

I have to assume you are willing to settle for slightly different variable names in the result.  After all, X'' is not a valid name for a variable in SAS (at least not by default it isn't).

Here's one way ... not necessarily fastest but probably easiest to understand:

proc transpose data=have prefix='X' out=transposed_X (drop=_name_);

var x;

by hospital;

run;

proc transpose data=have prefix='Y' out=transposed_Y (drop=_name_);

var y;

by hospital;

run;

proc transpose data=have prefix='Z' out=transposed_Z (drop=_name_);

var z;

by hospital;

run;

data want;

merge transposed_x transposed_y transposed_z;

by hospital;

run;

Note that this solution assumes your data are already sorted by hospital.

Good luck.

Solution
‎01-15-2015 05:04 PM
Posts: 5,540

## Re: "Concatenate" observations by group

Another way :

data have;

length hospital X Y Z \$8;

input obs hospital  X Y Z;

datalines;

1          a          X1     Y1    Z1

2          a          X2     Y2    Z2

3          a          X3     Y3    Z3

4          b          X4     Y4    Z4

5          b          X5     Y5    Z5

;

data temp / view=temp;

length id idlabel \$32;

do i = 1 by 1 until(last.hospital);

set have; by hospital;

array t{*} X--Z;

do j = 1 to dim(t);

id = catx("_", vname(t{j}), i);

idlabel = cats(vname(t{j}), substrn(repeat("'", i),3));

var = t{j};

output;

end;

end;

keep hospital id idlabel var;

run;

proc transpose data=temp out=want(drop=_;

by hospital;

id id;

idlabel idlabel;

var var;

run;

proc print data=want label noobs; run;

PG

PG
Super Contributor
Posts: 490

## Re: "Concatenate" observations by group

data have;

input hospital \$ X \$ Y  \$ Z \$;

cards;

a X1 Y1 Z1

a X2 Y2 Z2

a X3 Y3 Z3

b X4 Y4 Z4

b X5 Y5 Z5

;run;

proc transpose data=have out=want1;

by hospital;

var x -- z;

run;

proc transpose data=want1 out=want2 ;

by hospital _name_;

var col1 - col3;

run;

data want2;

set want2;

by hospital _name_;

retain i ;

if first._name_ then i=1;

_name_=cats(_name_,i);

i=i+1;

run;

proc transpose data=want2 out=want3 (drop=_name_) ;

by hospital;

var col1;

run;

Super User
Posts: 10,787

## Re: "Concatenate" observations by group

The simplest way is using proc means + idgroup .

If you want to fast speed , I would like to use MERGE statement.

```data have;
input hospital \$ X \$ Y  \$ Z \$;
cards;
a X1 Y1 Z1
a X2 Y2 Z2
a X3 Y3 Z3
b X4 Y4 Z4
b X5 Y5 Z5
;
run;
proc sql noprint;
select max(n) into : n
from (select count(*) as n from have group by hospital);
quit;
proc summary data=have;
by hospital;
output out=want(drop=_:) idgroup(out[&n] (x y z)= );
run;

```

Xia Keshan

Super User
Posts: 10,787

## Re: "Concatenate" observations by group

Alternative way is MERGE statement, my absolutely favorite .

```data have;
input hospital \$ X \$ Y  \$ Z \$;
cards;
a X1 Y1 Z1
a X2 Y2 Z2
a X3 Y3 Z3
b X4 Y4 Z4
b X5 Y5 Z5
;
run;
data have;
set have;
by hospital;
if first.hospital then n=0;
n+1;
run;
proc sql;
select distinct catt('have(where=(hospital="',hospital,'" and n=',n,') rename=(x=x_',n,' y=y_',n,' z=z_',n,'))') into : list separated by ' '
from have;
quit;
data want;
merge &list;
by hospital;
run;

```

Xia Keshan

Message was edited by: xia keshan

Occasional Contributor
Posts: 16

## Re: "Concatenate" observations by group

Thank you all! I wish I could mark "Correct answer" to all.

Super User
Posts: 10,787

## Re: "Concatenate" observations by group

Another way :

```data have;
length hospital X Y Z \$8;
input obs hospital  X Y Z;
drop obs;
datalines;
1          a          X1     Y1    Z1
2          a          X2     Y2    Z2
3          a          X3     Y3    Z3
4          b          X4     Y4    Z4
5          b          X5     Y5    Z5
;
run;
data have;
set have;
by hospital ;
if first.hospital then n=0;
n+1;
run;
proc transpose data=have out=temp;
by hospital n;
var x y z;
run;
proc transpose data=temp out=want(drop=_name_);
by hospital;
var col1;
id _NAME_ n;
run;

```

Xia Keshan

🔒 This topic is solved and locked.