Call Execute a macro from data statement

[thexlwiz]

Hi All,

I am trying to execute a macro that builds a temporary VBS script based on some parameters stored in the data table. 3 lines, 3 executions. I am getting: ERROR 180-322: Statement is not valid or it is used out of proper order.

Can you please help!

TIA

GG

/*output*/ filename txtout   "C:\Documents and Settings\&sysuserid\My Documents";
/*input*/ filename ylds   "C:\Documents and Settings\&sysuserid\My Documents\yields.csv";
libname sasds "H:\SASWork\MGM_BATCH\data\sasds";

%macro Run_MGM(mgmfile,cp,sched,startage,startyr);
   script   = catx('\',pathname('WORK'),'MGM_RUN.vbs');
   filevar  = script;
   /* write the script */
   file dummy1 filevar=filevar;

   put "Const workbook=""&mgmfile.""";
   put "Const txtout=""C:\Documents and Settings\&sysuserid.\My Documents""";
   put "Const xlCSV = 6";
   put 'Set objExcel = CreateObject("Excel.Application")';
   put 'With objExcel';
   put +3 '.Visible = False';
   put +3 'Set objWorkbook  = .Workbooks.Open(workbook)';
   put +3 'objWorkbook.Close';
   put +3 '.Application.Quit';
   put 'End With';

   /* close the script file by opening another, not used */
   filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
   file dummy1 filevar=filevar;
   /* look at the script, not necessary but may be useful */
   infile dummy2 filevar=script end=eof;
   do while(not eof);
      input;
      putlog _infile_;
   end;
   /* call the script */
   command = catx(' ','cscript',quote(strip(script)),'//nologo');
   infile dummy3 pipe filevar=command end=eof truncover;
%mend Run_MGM ;

data mgm_runs;
set sasds.mgm_runs;
if runid gt 0;
mgmwb=trim(mgm_wb_path) || "\" || trim(mgm_wb_name);
run;

data _null_;
set mgm_runs;
call execute('%Run_MGM('||mgmwb||','||trim(cp_name)||','||trim(schedule)||','||startage||','||startyear||')');
run;

quit;

[Tom · Accepted solution]

Your macro is only generating part of a data step.  You either need to generate the rest of the data step with other CALL EXECUTE() statements or add those lines to your macro.

So you are running this step

data _null_;
set mgm_runs;
call execute('%Run_MGM('||mgmwb||','||trim(cp_name)||','||trim(schedule)||','||startage||','||startyear||')');
run;

It will push code onto the stack to run after it finishes.  The first couple of lines your macro call generates are assignment statements:

   script   = catx('\',pathname('WORK'),'MGM_RUN.vbs');
   filevar  = script;

You cannot just run an assignment statement like that without first starting a data step.  So you will need to at least a DATA statement.

 Why not just add these two lines to your macro. One at the top and one at the bottom. So that your macro is now generating a complete data step.

data _null_;
run;

[thexlwiz]

Thank you so much!