Thank you all very much for your feedback! Unfortunately I was not able to get the array recommendations to work, but transposing the data did work. I am not permitted to share the data publicly -- that is why I did not post it, and I absolutely understand that not seeing the data made answering my question much more complicated. This is what the code I ended up using: Var_umbrella = trim(left(substr(Var_umbrella,2))); seq=0; do while(Var_umbrella ~= " "); if index(Var_umbrella, "|" )>0 & Var_umbrella ~in("|","||") then do; piece = trim(left(substr(Var_umbrella,1,index(Var_umbrella,"|")-1))); Var_umbrella = trim(left(substr(Var_umbrella,index(Var_umbrella,"|")+1))); seq=seq+1; output; end; else if Var_umbrella in("|","||") then Var_umbrella=" "; else do; piece = trim(left(Var_umbrella)); Var_umbrella = " "; seq=seq+1; output; end; end; run; data want_1; set data have; length vname $50 value $500; vname = trim(left(substr(piece,1,index(piece," ")-1))); value = trim(left(substr(piece,index(piece," ")+1))); run; proc sort data=want_1; by case; run; data want_2; set want_1; by case; retain seq_off; if first.case=1 then seq_off=1; else if vname="Var_1" then seq_off = seq_off+1; run; proc transpose data=want_most out=want_table; by case; id vname; var value; run;
... View more