DATA Step, Macro, Functions and more

Transpose large dataset with each row as a question response

Reply
Frequent Contributor
Posts: 78

Transpose large dataset with each row as a question response

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.

PROC Star
Posts: 7,471

Transpose large dataset with each row as a question response

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;

Frequent Contributor
Posts: 78

Transpose large dataset with each row as a question response

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?

PROC Star
Posts: 7,471

Transpose large dataset with each row as a question response

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.

Frequent Contributor
Posts: 78

Transpose large dataset with each row as a question response

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.

Respected Advisor
Posts: 3,799

Transpose large dataset with each row as a question response

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=_Smiley Happy prefix=Question;

   by submitid;

   id questionid columnid;

   var answerid;

   format columnid cid.;

   run;

proc contents varnum;

proc print;

   run;

Frequent Contributor
Posts: 78

Transpose large dataset with each row as a question response

Posted in reply to data_null__

Thank you, it works great!

Ask a Question
Discussion stats
  • 6 replies
  • 196 views
  • 0 likes
  • 3 in conversation