SAS dynamic variable names from other variables

Question

I am trying to create SAS variable names based on data contained within other variables. For example, I could start with

Obs  Var1  Var2
  1   abc     X
  2   def     X
  3   ghi     Y
  4   jkl     X

and I would like to end up with

Obs  Var1  Var2    X    Y
  1   abc     X   abc   
  2   def     X   def   
  3   ghi     Y        ghi
  4   jkl     X   jkl

I do have one way of doing this but it requires somewhat ugly macros to first create the variables needed (using a length statement) and then creating a whole series of numbered macro variables (1 per observation) that are later called inside a data step loop. It works but is complicated and I don't think will scale well to the real data, which contain multiple variables for creation per row, and a few thousand rows.

I've also tried something with arrays - saving variables names in a macro var, using it to generate an array statement, and trying to keep track of which array index is needed for each new variable, but it is also complicated.

What would really help would be something analogous to

vvaluex(var2)=var1

except vvaluex can't be on the left-hand side of an equals. Any thoughts or ideas?

Joe Joe · Accepted Answer · 2013-04-18T18:08:33

PROC TRANSPOSE is a handy way to do the example in the question.

data have;
input Obs  Var1 $  Var2 $;
datalines;
1   abc     X
2   def     X
3   ghi     Y
4   jkl     X
;;;;
run;

proc transpose data=have out=want;
by obs;
id var2;
var var1;
copy var1 var2;
run;

Another option is probably similar to what you've tried before, using arrays and VNAME:

proc sql;
select var2 into :var2list separated by ' ' from have;
quit;

data want;
set have;
array newvars $ &var2list;
do _t = 1 to dim(newvars);
  if vname(newvars[_t]) = Var2 then do;
    newvars[_t] = var1;
    leave;
  end;
end;
run;

PROC TRANSPOSE should be faster and is probably more flexible, but this might work better for some purposes.

SAS dynamic variable names from other variables

1 Answers