BookmarkSubscribeRSS Feed
statadm
Fluorite | Level 6

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.

6 REPLIES 6
art297
Opal | Level 21

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
Fluorite | Level 6

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
Opal | Level 21

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
Fluorite | Level 6

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__
Jade | Level 19

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
Fluorite | Level 6

Thank you, it works great!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1009 views
  • 0 likes
  • 3 in conversation