How to tell SAS to run a macro that is contained in a Column of table

[Question]

Hi,

I have created a macro in a column A called " final_macro" in the table attached (Macro_query).

I would like to tell SAS to run it from the column A automatically?

Is it possible?

Thank you  🙂

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

If by "column of table" you mean "a variable in a SAS data set" the answer is without looking in that spread is probably.

A data step can use text in data set, or constructed text, with the CALL Execute statement to submit the text for execution.

However, you are going to have to clarify what you mean by "automatically". Automatically when what happens?

[Tom · Accepted solution]

You put CODE into an XSLX file?  Why?

I jumped through the hoops needed to look at your attached file and it appears to CALLS to a macro in each cell.  So not a macro definition at all, just just normal SAS code that happens consist of macro calls.

The cleanest way is to write the code to a file and run the file.

So assuming the dataset you get from the spreadsheet is named CALLS and the column with the SAS code is named CALL then use code like this to write that to a file and then use %INCLUDE to run it.

filename code temp;
data _null_;
   set calls;
   file code;
   put call;
run;
%include code / source2;

[Question]

Thank you Tom, for this.....That gives me what I need exactly, it works  😀

Best wishes

[Patrick]

I personally would use the approach @Tom proposes because it creates the "cleanest" log. An alternative would be call execute() or the dosubl() function.

%macro test();
  %global i;
  %let i=%eval(&i+1);
  data _null_;
    var=&i;
    put var=;
  run;
%mend;

data macro_calls;
  infile datalines truncover;
  input code $100.;
  datalines4;
%test();
%test();
%test();
%test();
;;;;

data _null_;
  set macro_calls;
  call execute(code);
/*  rc=dosubl(code);*/
run;