Hi everyone. I was given a data in below structure:
Name Age Hobby Color
Ben 12 Movie, Study Blue, Red
Fenny 27 Jogging, Eating White, Red
and was asked to convert into this format:
Name Age Hobby Color
Ben 12 Movie Blue
Ben 12 Movie Red
Ben 12 Study Blue
Ben 12 Study Red
Fenny 27 Jogging White
Fenny 27 Jogging Red
Fenny 27 Eating White
Fenny 27 Eating Red
I have been doing research for the past 3 hours and i do not find any straight forward answer to that.
I was thinking to use below logic, which i havent tried yet as my work PC is not at home today.
First step, find way to split the delimiter comma. Then, if first.name then populate the value in delimiter into column color and hobby.
However, I really cannot think of the proper script for that. Can anyone give some hint on the script needed? even if there is no full code is fine, as i just need some logic explanation.
data want;
set have;
if first.name then do;
end;
run;
Scenario 2
I have another new file and i was asked to generate another output.
Name feeder_in feeder_out NickName
ABBA 1,2 A,B ABBA
POLA 1,2 C,D,E CONS POLA
and the desire output:
Name feeder_final
ABBA 1
ABBA 2
ABBA A
ABBA B
POLA 1
POLA 2
CONS POLA C
CONS POLA D
CONS POLA E
This is different from the earlier scenario.
Does anyone have clue for scenario 2?
For each record, count the number of hobbies, count the number of colors, and then for each color/hobby combination, output a new record.
/* UNTESTED CODE */
data want;
set have;
length newhobby newcolor $ 10;
do i=1 to countw(hobby,',');
do j=1 to countw(color,',');
newhobby=scan(hobby,i,',');
newcolor=scan(color,j,',');
output;
end;
end;
drop i j hobby color;
run;
For each record, count the number of hobbies, count the number of colors, and then for each color/hobby combination, output a new record.
/* UNTESTED CODE */
data want;
set have;
length newhobby newcolor $ 10;
do i=1 to countw(hobby,',');
do j=1 to countw(color,',');
newhobby=scan(hobby,i,',');
newcolor=scan(color,j,',');
output;
end;
end;
drop i j hobby color;
run;
Hi @PaigeMiller and @Kurt_Bremser . Thanks for the code. It is working perfectly. However, would it be able to tweak to cater for Scenario 2 that i just put in in my first post?
@imdickson wrote:
Hi @PaigeMiller and @Kurt_Bremser . Thanks for the code. It is working perfectly. However, would it be able to tweak to cater for Scenario 2 that i just put in in my first post?
It is so much easier to follow when new information is added at the bottom of the thread, rather than at the top. Could you please make Scenario 2 the next message in this thread?
The principle is the same, just adapt the code.
data want;
set have (rename=(hobby=hobby_old color=color_old));
do hc = 1 to countw(hobby_old,',');
hobby = scan(hobby_old,hc,',');
do cc = 1 to countw(color_old,',');
color = scan(color_old,cc,',');
output;
end;
end;
drop color_old hobby_old hc cc:
run;
Just two nested loops and use of countw() and scan().
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.