@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.
