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 = ".";
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.