"Concatenate" observations by group

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

"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
Respected Advisor
Posts: 4,609

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=_Smiley Happy;

by hospital;

id id;

idlabel idlabel;

var var;

run;

proc print data=want label noobs; run;

PG

PG

View solution in original post


All Replies
Respected Advisor
Posts: 4,998

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
Respected Advisor
Posts: 4,609

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=_Smiley Happy;

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;

Grand Advisor
Posts: 9,593

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

Grand Advisor
Posts: 9,593

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.

Grand Advisor
Posts: 9,593

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.

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

Discussion stats
  • 7 replies
  • 452 views
  • 8 likes
  • 5 in conversation