How do i split by comma and transpose?

[imdickson]

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?

[PaigeMiller · Accepted solution]

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;

[imdickson]

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?

[PaigeMiller]

---

@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?

[Kurt_Bremser]

The principle is the same, just adapt the code.

[Kurt_Bremser]

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().