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.
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
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
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?
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;
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 = ".";
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.