@superbug Try this. It works with the data you provided.
data have;
input item_id $ resp;
datalines;
PP1111 1
PP1111 2
PP1111 3
pp2222 1
pp2222 2
pp2222 3
pp2222 4
pp3333 1
pp3333 3
pp3333 4
pp4444 1
pp4444 2
pp5555 1
;
run;
proc sql noprint;
select substr(max(item_id),3,1) into: max from have;
quit;
data arr (keep=a: i);
array arr{&max.,4};
set have;
i = input(substr(item_id,3,1), 1.);
arr{i,resp} = resp;
run;
proc transpose data=arr out=arr_trans name=arr_num;
by i;
run;
data want (keep=item_id resp);
set arr_trans;
var = coalesce(col1, col2, col3, col4);
item_id = cat('pp', put(i,1.), put(i,1.), put(i,1.), put(i,1.));
resp = var;
where (arr_num = "arr"||strip(put(((i-1)*4+1),2.))
or arr_num = "arr"||strip(put(((i-1)*4+2),2.))
or arr_num = "arr"||strip(put(((i-1)*4+3),2.))
or arr_num = "arr"||strip(put(((i-1)*4+4),2.)));
run;
This code could certainly be cleaned up a lot. This is not a very dynamic approach. I have no doubt there is a cleaner solution.
Edited to remove unnecessary code and reduce hard-coding.
... View more