topic Transpose large dataset with each row as a question response in SAS Programming

Transpose large dataset with each row as a question response

statadm — Thu, 04 Aug 2011 14:49:09 GMT

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.

Transpose large dataset with each row as a question response

art297 — Thu, 04 Aug 2011 14:55:55 GMT

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;

Transpose large dataset with each row as a question response

statadm — Thu, 04 Aug 2011 16:53:24 GMT

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?

Transpose large dataset with each row as a question response

art297 — Thu, 04 Aug 2011 18:02:59 GMT

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.

Transpose large dataset with each row as a question response

statadm — Thu, 04 Aug 2011 18:09:10 GMT

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.

Transpose large dataset with each row as a question response

data_null__ — Thu, 04 Aug 2011 18:24:50 GMT

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;

Transpose large dataset with each row as a question response

statadm — Thu, 04 Aug 2011 19:18:38 GMT

Thank you, it works great!