TRASPOSE INTO ONE ROW

[Andres_Fuentes1]

Dear, I hope you are very well, I have a problem that I hope you can help me. My data is as follows, 

and I need to transform it in a single row by ID. like the following example.

thanks for your help. 

[yabwon · Accepted solution]

Hi, two approaches. 

Data:

data have;
input id $ profile $ type music $;
cards;
A NICE  1 ROCK
A SAD   2 JAZZ
A SAD   1 ROCK
A HAPPY 2 POP
B NICE  1 ROCK
B SAD   2 JAZZ
B SAD   1 ROCK
B HAPPY 2 POP
B HAPPY 3 METAL
;
run;
proc print;
run;

One:

proc transpose data = have out = have1 prefix=profile;
  by id;
  var profile;
run;
proc transpose data = have out = have2 prefix=type;
  by id;
  var type;
run;
proc transpose data = have out = have3 prefix=music;
  by id;
  var music;
run;

data want;
  merge have1 have2 have3;
  by id;
run;
proc print;
run;

Two:

data have_v / view = have_v;
  set have;
  by id;
  if first.id then order = 0;
  order + 1;
run;
proc print data = have_v;
run;


proc transpose data = have_v out = have_v2;
  by ID order;
  var profile type music;
run;
proc print;
run;

proc transpose data = have_v2 out = have_v3(drop = _name_) ;
  by ID;
  var col1;
  id _name_ order;
run;
proc print;
run;

All the best

Bart

[PaigeMiller]

You might consider leaving the data in a long data set, as many times this is easier to work with than a wide data set. What tasks are you planning on performing after the TRANSPOSE?

[Ksharp]

data have;
input id $ profile $ type music $;
cards;
A NICE  1 ROCK
A SAD   2 JAZZ
A SAD   1 ROCK
A HAPPY 2 POP
B NICE  1 ROCK
B SAD   2 JAZZ
B SAD   1 ROCK
B HAPPY 2 POP
B HAPPY 3 METAL
;
data have;
 set have;
 by id;
 if first.id then n=0;
 n+1;
run;
proc sql noprint;
select distinct catt('have(where=(n=',n,') rename=(
 profile=profile',n,' type=type',n,' music=music',n,'))') into : merge separated by ' '
from have;
quit;
data want;
 merge &merge;
 by id;
 drop n;
run;

[Kurt_Bremser]

By adding a profile count, you can have PROC REPORT create output along your intentions:

data have;
input id $ profile $ type music $;
cards;
A NICE  1 ROCK
A SAD   2 JAZZ
A SAD   1 ROCK
A HAPPY 2 POP
B NICE  1 ROCK
B SAD   2 JAZZ
B SAD   1 ROCK
B HAPPY 2 POP
B HAPPY 3 METAL
;

data extend;
set have;
by id;
if first.id
then profct = 1;
else profct + 1;
run;

options missing = " ";

proc report data=extend;
column id profct,(profile type music) n;
define id / group;
define profile / display;
define type / display;
define music / display;
define profct / "" across;
define n / noprint;
run;

options missing = ".";