Call execute in macro programme won't change the data file

Question

I program this macro, who uses a call execute to put label of my old variable on my new variable. But, when I use the macro on different dataset, the call execute don't change the dataset.

%MACRO CHARTONUM2(file=,var=,nbvar=,newvar=,fmt=);
%DO aa=1 %TO &nbvar;
    DATA &file;
        set &file end=eof;
        &newvar&aa=input(put(&var&aa,$3.),3.);
        format &newvar&aa &fmt..;
        if eof then do;
            call execute('data &syslast; set &syslast;');
            call execute('label &newvar&aa = "' || strip(vlabel(&var&aa)) || '";');
            call execute('run;');
        end;
        drop &var&aa;
    RUN;
%END;
%MEND CHARTONUM2;

So, the dataset is dynamic with parameter "file". If I run this macro twice with 2 differents dataset, the call execute will search my variable in the 1st dataset. The rest is running good. Here is the log :

%CHARCHARTONUM(file=demohealth,var=SEX,deb=1,end=1,newvar=sexn,fmt=
sex);
MPRINT(CHARCHARTONUM):   DATA demohealth;
MPRINT(CHARCHARTONUM):   set demohealth end=eof;
MPRINT(CHARCHARTONUM):   sexn=input(put(SEX, $sex.),3.);
MPRINT(CHARCHARTONUM):   format sexn sex.;
MPRINT(CHARCHARTONUM):   if eof then do;
MPRINT(CHARCHARTONUM):   call execute('data &syslast; set &syslast;');
MPRINT(CHARCHARTONUM):   call execute('label &newvar = "' ||
strip(vlabel(SEX)) || '";');
MPRINT(CHARCHARTONUM):   call execute('run;');
MPRINT(CHARCHARTONUM):   end;
MPRINT(CHARCHARTONUM):   drop SEX_STD;
MPRINT(CHARCHARTONUM):   RUN;

WARNING: The variable SEX_STD in the DROP, KEEP, or RENAME list has never
         been referenced.
MPRINT(CHARCHARTONUM):   data WORK.ASIPRE ;
MPRINT(CHARCHARTONUM):   set WORK.ASIPRE ;
MPRINT(CHARCHARTONUM):   label sexn = "Sex";
MPRINT(CHARCHARTONUM):   run;

I want to launch this macro on any dataset... Is somebody have an idea ?

Answer 1

Don't rewrite the entire data set just to change a label -- that is un-needed I/O and can be really expensive if the data set is large. Perform all your transforms in one pass, and at the same time generate a dynamic label statement into a macro variable for use in a subsequent PROC DATASETS that assigns the new label permanently.

Repeated calls to numToChar to do one variable at a time is also excessive I/O.

When you want to transform multiple variables as a replicate in their format rendered representation, consider passing space separated lists as your macro parameters; a list of variable names, a list of the new names and a list of the rendering formats. However, in many cases, you may find that a naming convention and processing variables of common role might require only the list of variables and the common format to apply.

Finally, the need to store a variable in a new one as it's format rendered representation might be extraneous. A review of the variable and it's downstream utilization could indicate you only need to have the format applied to the original variable.

Sample code

data have;
  x=12; y=12.34; z=0.007;
  label 
    x = 'The value of X'
    y = 'Y?, ask Mickey'
    z = 'Zed''s "bike"'
  ;
run;

%macro numToChar(data=, out=&data, vars=, newvars=, formats=);
  %local var newvar fmt labels lib mem;

  data &out;
    set &data;
    %do i = 1 %to %sysfunc(countw(&vars,,S));
      %let var    = %scan(&vars,&i,,S);
      %let newvar = %scan(&newvars,&i,,S);
      %let fmt    = %scan(&formats,&i,,S);

      &newvar = put(&var,&fmt);

      call symput('labels', catx(' ', symget('labels'), "&newvar=" || quote(trim(vlabel(&var)))));
    %end;
  run;

  %let lib = %scan(&syslast,1,.);
  %let mem = %scan(&syslast,2,.);

  proc datasets nolist lib=&lib;
    modify &mem;
    label &labels;
  run;
  quit;
%mend;

options mprint;

%numToChar(
  data=have,
  out=want,
  vars=x y z,
  newvars=x_best y_best z_best,
  formats=best. best. best.
);

Answer 2

It is a timing issue. The value of &syslast is evaluated when the code generated by call execute() is pushed onto the stack. You can use %nrstr() to delay the evaluation until the code is pulled off of the stack to be executed.

call execute('%nrstr(data &syslast; set &syslast;)');

Run this example to see it in action.

data one; x=1; run;
data two; x=2;
  call execute('data _null_; set &syslast; put x=; run;');
  call execute('data _null_; set %nrstr(&syslast); put x=; run;');
run;

Log

NOTE: CALL EXECUTE generated line.
1   + data _null_; set WORK.ONE                             ; put x=; run;

x=1
NOTE: There were 1 observations read from the data set WORK.ONE.


2   + data _null_; set &syslast; put x=; run;

x=2
NOTE: There were 1 observations read from the data set WORK.TWO.

But since you are already running a macro the real solution is to modify your macro so that you don't need to use call execute. You can also create the new variables in one pass of the data. And eliminate the unneeded PUT() function call. And make the attachment of a format to the new variable(s) more flexible or even optional.

%MACRO CHARTONUM2(file=,var=,nbvar=,newvar=,fmt=);
%if %length(&fmt) and not %index(&fmt,.) %then %let fmt=&fmt..;
data _null_;
  set &file ;
%DO aa=1 %TO &nbvar;
  call symputx("label&aa",vlabel(&var&aa),'L');
%END;
  stop;
run;

date &file;
  set &file end=eof;
%DO aa=1 %TO &nbvar;
  &newvar&aa=input(&var&aa,32.);
  format &newvar&aa &fmt;
  label &newvar&aa="&&label&aa" ;
  drop &var&aa;
%END;
run;
%MEND CHARTONUM2;

Answer 3

It has to do with when the value of &SYSLAST is updated and when the compilation of that data step happens.

The compilation of the CALL EXECUTE data step happens before the inner data step is finished. The value of &SYSLAST has not been updated. Your version that works is because the value of &FILE is the same as &SYSLAST

You can see it with this example:

data test;
x=1;
label x="X Label";
run;

data temp;
y=2;
label y="Y Label";
run;

%macro NUMTOCHAR(file=,var=,newvar=,fmt=best.);
data &file;
set &file end=eof;
&newvar=put(&var,&fmt);

if eof then do;
    call execute('data &syslast; set &syslast;');
    call execute('label &newvar = "' || strip(vlabel(&var)) || '";');
    call execute('run;');
end;
run;

%mend;

%numtochar(file=test,var=x,newvar=xc);

You see that the second data step is working on temp and not test.

Change your code to:

call execute('data &file; set &file;');