SAS-Writing Multiple Tables to one XLSX Workbook w/ 2 tables per sheet

0
votes

I am new to SAS and am having some issues exporting data. I have written a macro to generate some summary tables based on a certain ID. The macro creates two tables for each ID identified in a particular proc sql query. I can write out the last two tables but it overwrites all tables. I was wondering if there is a way to generate one sheet, containing the two summary tables, for each ID identified in my query. Below is the code I have to date for exporting data:

%macro output(x);
ods tagsets.excelxp file="W:\user\test.xls" options(sheet_interval='none');

proc print data=prov_&x;
run;

proc print data=prov_revcd_&x;
run;

ods tagsets.excelxp close;
%mend;


/*Run a loop for each IDcode. Each code will enter the document generation loop*/
%macro loopit(mylist);
    %let else=;
   %let n = %sysfunc(countw(&mylist)); /*let n=number of codes in the list*/
    data 
   %do I=1 %to &n;
      %let val = %scan(&mylist,&I); /*Let val= the ith code in the list*/
    %end;

   %do j=1 %to &n;
      %let val = %scan(&mylist,&j); /*Let val= the jth code in the list*/
/*Run the macro loop to generate the required tables*/
%runtab(&val);
%output&val);
   %end;
   run;
%mend;

/*Run the macro loop over the list of significant procedure code values*/
%loopit(&varlist);

Any help for correcting this issue would be greatly appreciated! Thanks!

2
sassas-macro
What version of SAS do you have? - Joe
And any particular reason you're using ODS instead of PROC EXPORT? - Joe
I am using SAS 9.3. No particular reason using ODS, if it can be done using PROC EXPORT that is fine. - Brad
It doesn't have any bearing on this problem - just wondernig if there was a reason. (PROC EXPORT produces xlsx files, what you have up there doesn't actually). - Joe

2 Answers

0
votes

Move the ods tagsets.excelxp file= and ods tagsets.excelxp close to outside of the macro otherwise you're recreating the file each time.

You may want to explicitly name the sheets as well.

0
votes

I would rewrite %output like so.

%macro output(x);
ods tagsets.excelxp options(sheet_interval='none' sheet_name="&x");

proc print data=prov_&x;
run;

proc print data=prov_revcd_&x;
run;    
%mend;

Then as Reeza suggests put the original ods tagsets.excelxp file= ... and close outside the whole macro.

ods tagsets.excelxp file="c:\temp\test.xlsx";
%loopit(&varlist)
ods tagsets.excelxp close;

If you use PROC EXPORT, that does allow apending to a workbook without this step (and no ODS at al).

%macro output(x);
proc export data=prov_&x outfile="c:\temp\test.xlsx" dbms=excel replace;
  sheet="&x._prov";
run;
%mend;

However, this only allows one dataset per sheet - so either you append them together first as a single dataset, or you use 2 sheets in this solution.