Transpose a table and split rows into new columns

Traian — Mon, 29 Nov 2021 09:40:48 GMT

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.

Re: Transpose a table and split rows into new columns

yabwon — Mon, 29 Nov 2021 10:04:49 GMT

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

topic Transpose a table and split rows into new columns in SAS Programming

Transpose a table and split rows into new columns

Re: Transpose a table and split rows into new columns