SAS Symput in a loop not working at first iteration

[fuatengin]

Hi All,

I run below code to extract all the numeric columns and name them temp_1, temp_2, temp_3 and so on. But I noticed that it does not output all the columns but the last one only. Beside that, temp_&b. is not working at first iteration, it outputs an error.

Can you help solve this? Thanks.

The code:

data new;

set work.test;

b=1;

array vars _numeric_;

do i=1 to 10;

call symputX('b',b);

temp_&b. = vars[i];

b = symget('b')+1;

call symputX('b',b);

c=i;

end;

run;

[Tom]

When the data step is compiled the macro references, like &B, are resolved before the step starts to run. So the value of a macro varaible generated during a data step cannot be used to generate any of the SAS code for the step that is currently running.  

So if you run this code you will create a variable name FRED and not one named SAM.

%let name=FRED;
data want;
  call symputx('name','SAM');
  &name = 10;
run;

What are you actually trying to do with that program?  Please post example input and output data.

[Quentin]

If you code (in a new session):

data _null_;
  b=1;
  call symputX('b',b);
  temp_&b=2;
run;

It will not work:

45   data _null_;
46     b=1;
47     call symputX('b',b);
48     temp_&b=2;
       -----
       180
WARNING: Apparent symbolic reference B not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

49   run;

It will not work because when the data step  is being compiled, the the macro processor sees the reference to the macro variable b and tries to resolve it.  But there is no macro variable named b yet, because the CALL SYMPUTX statement has not executed yet.  This is why the general rule is you cannot use CALL SYMPUTX to create a macro variable and refer to the macro variable in the same step.  The timing doesn't work out because the macro variable reference is resolved (or attempted to resolve) before CALL SYMPUTX has executed.

SYMGET allows you to look up the value of a macro variable during data step execution time.  Which is why the following will work:

55   data _null_;
56     b=1;
57     call symputX('b',b);
58     b = symget('b')+1;
59     put b=;
60   run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      58:7
b=2

Can you say more about your big picture goal?  Is it that you want to rename the variables, or do you want an array of temporary variables, or something else?

[RW9]

I agree with @Quentin, providing more context would allow better answers.  For instance another method would be to normalise the data, i.e. transpose all the numeric variables down, change to whatever you need then transpose again, avoiding loops and such like, for ex:

proc transpose data=have out=inter;
  by <idvars here>;
  var _numeric_;
run;

data inter;
  set inter;
  /* code to change _name_ to whatever you need */
run;

proc transpose data=interout=want;
  by <idvars here>;
  var ...;
  id _name_;
run;

[Astounding · Accepted solution]

 Is there any reason you can't use the original variables instead of "extracting" them to new variables?

You seem to be aiming for the first 10 variables only, since your DO loop runs from 1 to 10.  For that, no macro language is needed:

data want;

set have;

array vars {*} _numeric_;

array temp_ {10};

do i=1 to 10;

   temp_{i} = vars{i};

end;

run;

A few notes about that ...

If you really want macro variables as we, you can still add a second statement inside the loop:

call symputx('temp_' || left(put(i,2.)), vars{i});

However such macro variables are probably useless since they get replaced on every observation.

Finally, what if you don't know how many numeric variables there are, and you want all of them instead of hard-coding "10"?  One PROC TRANSPOSE solution has been proposed.  While I haven't verified the code, it's definitely a possibility.  Alternatively, you could capture that number as a macro variable and use a two-DATA-step solution:

data _null_;

set have;

array vars {*} _numeric_;

call symputx('n_nums', dim(vars));

stop;

run;

Then &N_NUMS is the number of numeric variables, and you can plug that into the second DATA step in both places that "10" now appears.