Execute Excel Commands from SAS

There are a few ways to execute Excel commands from SAS, but this method uses DDE (“Dynamic Data Exchange”) to facilitate communication between the two applications. For this example, I want to open 25 multisheet XML workbooks in Excel, call an Excel macro to run for each tab, and then re-save the XML files as XLSX files before closing them. In order to cycle through each of these XML output files, I also use a macro in SAS, which I run using call execute in a data step.

In the example below, I first set the system options noxwait and noxsync so that commands are executed as soon as they are issued (noxwait) and prompt windows are closed after commands are completed (noxsync). I then start Excel, using sleep to allow Excel enough time to process commands before SAS issues the next command. Next, I open the workbook which contains my macros, making sure to do all this outside of the SAS macro where I modify each of my XML files so that these steps only happen once.

options noxwait noxsync;
data _null_;
rc=system(‘start excel’);
run;
data _null_;
x=sleep(3);
run;
filename CMDS DDE ‘EXCEL|SYSTEM’;
data _null_;
file CMDS;
put %unquote(%str(%'[open(“Z:\Pharma_Informatics\Consulting\&comp1\&project\outputs\macros.xlsm”)]%’));
run;

Now that Excel is open and my macros are available for use, I launch into my SAS macro to cycle through all of my XML files and modify each of them in Excel. I set a few macro variables which I will use to reference the files and their location. Again, I use sleep to allow Excel enough time to process commands; otherwise, the process will end in errors. I run the macro for three tabs in each document, and then I save the XML file as an xlsx file. The second parameter in the save.as command depends on the type of file you want to create – here I use 51 which corresponds to the xlsx file type; you would use a different parameter if you wished to create a csv file or an xls file. Immediately prior to saving the file, I use the error(false) line to suppress any prompts in Excel (for example, asking me if I want to replace an existing file) so that the entire process can run without any intervention from me. Note that any time I want to reference a macro variable in a command, I have to use %unquote and %str, or it will not resolve properly. The final step in the SAS macro below closes the new Excel file.

%macro xlsave(cohort=,post_elig_months=);
%let OutputFileName = persis_&cohort._&post_elig_months;
%let xmlpath=Z:\Pharma_Informatics\Consulting\&comp1\&project\outputs\&outputfilename..xml;
%let savepath=Z:\Pharma_Informatics\Consulting\&comp1\&project\outputs\&outputfilename..xlsx;

data _null_;
file CMDS;
put %unquote(%str(%'[open(“&xmlpath”)]%’));
x=sleep(5);
put %unquote(%str(%'[workbook.activate(“&cohort._gp30”)]%’));
put %unquote(%str(%'[RUN(“macros.xlsm!post&post_elig_months”)]%’));
x=sleep(3);
put %unquote(%str(%'[workbook.activate(“&cohort._gp45”)]%’));
put %unquote(%str(%'[RUN(“macros.xlsm!post&post_elig_months”)]%’));
x=sleep(3);
put %unquote(%str(%'[workbook.activate(“&cohort._gp60”)]%’));
put %unquote(%str(%'[RUN(“macros.xlsm!post&post_elig_months”)]%’));
x=sleep(3);

put ‘[error(false)]’;
/*need second parameter or will save still xml format but w diff file extension*/
put %unquote(%str(%'[save.as(“&savepath”,51)]%’));
x=sleep(5);
put ‘[close()]’;
run;
%mend;

I actually have two different Excel macros I want to run, depending the value of SAS macro variable post_elig_months. To do this, I use proc sql to select the cohorts I want to run for post_elig_months = 24 months (from an existing table), and then I call the macro. I repeat the process for 12 months.

proc sql;
create table cohort24 as
select *
from apwrk.cohort
where
post_elig_months=24
;
data _null_; set cohort24;
call execute(‘%xlsave(cohort=’||cohort||’,post_elig_months=’||post_elig_months||’)’);
run;

proc sql;
create table cohort12 as
select *
from apwrk.cohort
where
post_elig_months=12
;
data _null_; set cohort12;
call execute(‘%xlsave(cohort=’||cohort||’,post_elig_months=’||post_elig_months||’)’);
run;

Finally, after I’m done, I close out of Excel by using the quit command.

data _null_;
file CMDS;
put ‘[quit()]’;
run;