topic Re: How to align two variables and make each values a variable in SAS Programming

How to align two variables and make each values a variable

dustychair — Mon, 22 Feb 2021 20:37:16 GMT

Hi all,

I hope I can explain what I want. I have a data set that includes question ids and students' responses to those questions. As seen below, question_id has 15 characters. The question_id variable needs to be separated into 5 characters and the response variable needs to be separated into 1 character. This part was easy, I used substr and it worked.

I want to make each question_ids a variable and align them with their responses. I don't know how to do it. Could you help me with that?

Thank you,

student_id question_id response

101 124541245512456 110

102 124561282512828 010

Want

student_id	q_12454	q_12455	q_12456	q_12825	q_12828
101	1	1	0
102			1	0	1

Re: How to align two variables and make each values a variable

ballardw — Mon, 22 Feb 2021 21:58:21 GMT

@dustychair wrote:

Hi all,

I hope I can explain what I want. I have a data set that includes question ids and students' responses to those questions. As seen below, question_id has 15 characters. The question_id variable needs to be separated into 5 characters and the response variable needs to be separated into 1 character. This part was easy, I used substr and it worked.

I want to make each question_ids a variable and align them with their responses. I don't know how to do it. Could you help me with that?

Thank you,

student_id question_id response

101 124541245512456 110

102 124561282512828 010

Want

student_id

q_12454

q_12455

q_12456

q_12825

q_12828

101

1

1

0

102

1

0

1

One way: assumes you want the values to be numeric. If not then use r= subst(response,i,1);

data have;
 input student_id $ question_id : $15. response :$3.;
datalines;
101 124541245512456 110
102 124561282512828 010
;

data need;
   set have;
   do i=1 to 3;
      qid = cats('q_', substr(question_id,((i-1)*5 +1),5));
      r= input(substr(response,i,1),best.);
      output;
   end;
   keep student_id qid r;
run;

proc transpose data= need
   out=want (drop=_name_)
;
   by student_id;
   id qid;
   var r;
run;

Caveat: if your "values" are ever more than 1 character out of that string then this gets WAY complicated as you need to provide exactly which "variable" may have the 2 character and conditionally extract 2 characters from the response. If your "response" changes lengths when the 2 character value appears then you may have some extremely long coding to address the change.

Comment: Beat the @#$%@#$% out of anyone that provides data in that sort of format.

Re: How to align two variables and make each values a variable

dustychair — Tue, 23 Feb 2021 02:34:21 GMT

Thank you for your response @ballardw. I am getting this error:
ERROR: The ID value "q_" occurs twice in the same BY group.

Re: How to align two variables and make each values a variable

dustychair — Tue, 23 Feb 2021 02:51:43 GMT

Also, I think the transpose statement will switch rows and columns so, some variables will be in the columns more than one time since some students took common questions. In the example that I posted q_12456 is a common question and it appears once in the data.

Re: How to align two variables and make each values a variable

andreas_lds — Tue, 23 Feb 2021 08:08:57 GMT

When i saw the "data" you have, my first thought was: if this is not homework, then somebody must take care of the person who "designed" this data structure, asap.

If student_id is not unique, please show what the result should look like in such case.

Re: How to align two variables and make each values a variable

ballardw — Tue, 23 Feb 2021 15:06:38 GMT

@dustychair wrote:
Thank you for your response @ballardw. I am getting this error:
ERROR: The ID value "q_" occurs twice in the same BY group.

Provide data. It doesn't for your example.

If your example is incomplete then the solution is likely to be incomplete.

Re: How to align two variables and make each values a variable

dustychair — Tue, 23 Feb 2021 15:36:46 GMT

I attached an example of data for just 3 people. I have 1.5 million responses like that.

Thank you