Hello! The reason your macro doesn't work is because the macro is compiled before the data step is processed, i.e. the countw(&temp) will translate into countw(Variable_source) = 1 (same with the %scan()-function). This is one way of doing it without using a macro: data Input;
input Variable_source $15.;
datalines;
01 03
01 03 04
01 05
01 02
;
/* Looping through each dynamic array (using scan() to loop through the "words" and outputing each source as a new row) */
/* Creating dummy variable transpose_var=1 for transpose below and temporary variable rownumber to avoid sorting issues when transposing */
data work.input_spliced(drop=i);
set work.input;
rownumber = _n_;
transpose_var = 1;
do i = 1 to countw(Variable_source);
Var_source_spliced = scan(Variable_source,i);
output;
end;
run;
/* Transposing the spliced table with variable prefix VARIABLE_ */
proc transpose data=work.input_spliced out=work.output(drop=rownumber _name_) prefix=VARIABLE_;
by rownumber Variable_source;
id Var_source_spliced;
var transpose_var;
run; Kind regards /Calle Edit: Realized there was some unnecessary code in there, so I trimmed the solution a bit. 🙂
... View more