Transpose large dataset with each row as a question response

[statadm]

I'm having trouble transposing a very large dataset where each answer is in a row, so each question has a unique id and each subject has a unique id and each response has a unique id.

submitid questionid answerid

1            1              1

1            2              1

1            3              2

1            4              3

1            5              2

2            1              2

2            2              1

2            3              1

2            4              1

2            5              2

I want the data to look like so:

submitid  question1 question2 question3 question4 question5

1             1             1             2              3             2

2             2             1             1              1             2

Thanks for your help in advance.

We were able to do this with many many proc transpose, but I figure there has to be a better way as this dataset is very large.

[art297]

As long as your data are sorted by submitid, you could use something like:

proc transpose data=have out=want prefix=question;

  by submitid;

  id questionid;

  var answerid;

run;

[statadm]

As it turns out my dataset is not as simple as I first thought.  I won't get anything unique until I put in another level.

submitid questionid answerid columnid

1            1              1            1

1            1              1            0

1            2              1

1            3              2

1            4              3

1            5              2

2            1              2

2            2              1

2            3              1

2            4              1

2            5              2

So essentially, if answerid isn't missing and columnid isn't missing, I will need to combine them to get the answer?

I don't know of a better way to transpose data that has a forth identifier to get a unique response.  The problem I have now is that questionid is not unique per subject if I have answerid and columnid.

Any thoughts?

[art297]

I think you need to show a full example with the new column filled in and how you would expect the resulting file to look.

[statadm]

submitid questionid answerid columnid

1            1              1            1

1            1              1            0

1            2              1

1            3              2

1            4              3

1            5              2

2            1              1            0

2            1              1            1

2            2              1

2            3              1

2            4              1

2            5              2

this is how the data looks.

I still need this:

submitid  question1a question1b question2 question3 question4 question5

1             1               0               1              2              3             2

2             0               1               1             1              1             2

But, if there is a response in the columnid column, this is the answer rather than the answerid.  The problem is when I transpose and id by questionid, I do not get unique question because some questions actually have subquestions that have the same questionid number.  The subquestion id number will be under answerid and the response is under columnid.

[data_null__]

data q;

   infile cards missover;

   input submitid questionid answerid columnid;

   columnID = coalesce(columnid,2);

   cards;

1            1              1            1

1            1              1            0

1            2              1

1            3              2

1            4              3

1            5              2

2            1              1            0

2            1              1            1

2            2              1

2            3              1

2            4              1

2            5              2

;;;;

   run;

proc format;

   value cid 1='a' 0='b' other=' ';

   run;

proc transpose out=wide(drop=_:) prefix=Question;

   by submitid;

   id questionid columnid;

   var answerid;

   format columnid cid.;

   run;

proc contents varnum;

proc print;

   run;

[statadm]

Thank you, it works great!