Transpose a table and split rows into new columns

[Traian]

I have a dataset similar to this:

ID    QUESTION    OBJECTS    ANSWER

23    E1                                      1

23    E2                                      2

23    E4                  5                  1

23    E4                  2                  2

24    E1                                      2

24    E4                  1                  1

24    E4                  4                  2                                 

25    E1                                      2

25    E2                                      3

25    E4                  3                  3

25    E5                                      3

...

This set is based on people's answers to a poll. For example E1 stands for what is your gender. Then you have the value ANSWER 1 for male and 2 for female. Then we have E4 that stands for "How often do you eat different type of chocolate?".  The ANSWER value will denote the type of chocolate and OBJECTS will denote the frequency. What i am trying to do is to transpose the set but i am having trouble with the double values.

This is what i am trying to achieve.

IDc  E1    E2    E3    E4_1    E4_2    E5

23   1       2               5          1

23   1       2               2          2 

24   2                        1          1

24   2                        4          2          3

25   2      3                3          3          3

...

As you can see the row E4 has been split in E4_1 and E4_2 because of the double values in OBJECTS and ANSWER. This is what i have trouble replicating.

PROC TRANSPOSE data = Choc_pol out = test let;
by IDc;
id QUESTION;
var OBJECTS ANSWER;
run;

This will obviously not work because i have to split the E4_1 and E4_2 rows first to properly assign the data.

[yabwon · Accepted solution]

something like this?

data have;
input ID    QUESTION $    OBJECTS    ANSWER;
cards;
23 E1 .  1
23 E2 .  2
23 E4 5 1
23 E4 2 2
24 E1 .  2
24 E4 1 1
24 E4 4 2
25 E1 .  2
25 E2 .  3
25 E4 3 3
25 E5 .  3
;
run;
proc print;
run;

proc sort data = have out = have2;
  by ID QUESTION;
run;
proc print;
run;

data have3;
  set have2;
  by ID QUESTION;
  if first.QUESTION then cnt = 0;
                    else cnt + 1;
run;
proc print;
run;

proc transpose data = have3 out = want DELIMITER=_;
by ID;
id QUESTION cnt;
var OBJECTS ANSWER;
run;
proc print;
run;

Bart