Macro to Output Multiple Excel Reports

[mae_day]

Hi all,

I have been tasked with automating our regular QA reports. The reports need to be Excel workbooks with five tabs (one for each type of incident report that has not been resolved). Each factory will receive their own QA report to address. I have already gotten the code to generate the output up and running, but need to figure out a way to generate a workbook for each distinct factory, rather than having it all in one.

The data includes these variables: Factory, Incident #, date, reported, corrected, and flag, where flag identifies the type of incident. There may be multiple rows for each factory depending on number of outstanding incident reports, so the data looks something like this:

Factory	Incident	Date	Report	Correct	Flag
A	120988274698	1/21/2021	‘Yes’	‘Unk/Missing’	2
A	741209882698	12/03/2020	‘Yes	‘Unk/Missing’	2
A	761200000698	08/17/2020	‘Unk/Missing’	.	1
B	332093900481	1/20/2021	‘No’	‘Unk/Missing’	4
C	593647400290	09/10/2020	‘Yes’	‘Unk/Missing’	2
C	118029374720	11/23/2020	‘Unk/Missing’	.	1

I know I will need to use a macro to make it run the code below for each factory name, but am unsure of how to implement this. I originally thought of splitting the dataset into a bunch of small ones for each factory, but that seems inefficient. Any thoughts?

Code for the output:

ods excel file="pathway\Report..xlsx" style=excel;
ods excel options(embedded_titles='yes' frozen_headers='4' flow='tables' sheet_name='Sheet_2’);

ods excel options(sheet_name='Reported’);
data _null_;
set all end=done;
where flag=2;
 if _n_ eq 1 then do;
 declare odsout t();
  	t.title(text: 'Incident Reported is Yes, Reconciliation is unk/missing', style_attr: 'fontsize=12pt fontweight=bold just=center vjust=center'); 
	t.title(data: factory, style_attr: 'fontsize=10pt fontweight=bold just=center vjust=center', start: 2);
		t.table_start();
			t.row_start();
				t.format_cell(text: 'Incident No.', overrides: 'fontweight=bold just=center vjust=center');
				t.format_cell(text: 'Date', overrides: 'fontweight=bold just=center vjust=center');
				t.format_cell(text: 'Reported?', overrides: 'fontweight=bold just=center vjust=center');
				t.format_cell(text: 'Corrected?', overrides: 'fontweight=bold just=center vjust=center');
			t.row_end();
		end;
			t.row_start();
				t.format_cell(data: incident);
				t.format_cell(data: %ExcelDateAdj(date), style_attr: 'tagattr="format:mm/dd/yyyy"');
				t.format_cell(data: report, format: '$report.');
				t.format_cell(data: correct, format:'$corrected.');
			t.row_end();
		if done then t.table_end();
run;

ods excel options(sheet_name=Sheet_3”);
data _null_;
set all end=done;
where flag=3;
…
		if done then t.table_end();
run;
ods _all_ close;

[Tom · Accepted solution]

So if your code works now to report all of the FACTORY values into the same workbook then you can probably very easily wrap it into a macro with one parameter, the FACTORY value.  Then use that parameter to build the Excel filename and also subset the data.  So something like this:

%macro report_one(factory);
ods excel file="pathway\&factory.Report..xlsx" style=excel;
...
data _null_;
  set all end=done;
  where flag=2;
  where also factory = "&factory";
....
%mend report_one;

Once you get that working just call the macro once for each factory.

data _null_;
  set all;
  by factory;
  if first.factory;
  call execute(cats('%nrstr(%report_one)(',factory,')'));
run;

[mae_day]

Thank you so much, this worked beautifully! I tried something like this, but got the syntax quite wrong. If I may ask, what does encasing the factory variable do in the line below? I'm still newish to using macros and desperately want to get better.

 call execute(cats('%nrstr(%report_one)(',factory,')'));

[Tom]

CATS() builds a string by concatenating all of its arguments (3 of them in this case).

CALL EXECUTE() submits code to run after the data step.

The single quotes prevent the %report_one macro call from running while the data _null_ step is being compiled.

The %NRSTR() wrapper around the %report_one reference is to prevent the macro from running while CALL EXECUTE is pushing the call onto the stack.  In your case all it is really doing is making the SAS log easier to read since now it only shows the calls to the macro as the statements that CALL EXECUTE generated instead of showing all of the statements that the macro generates for each call. 

But if the macro actually was using macro logic to make decisions on what code to run based on things that were changed by earlier SAS code that the macro generated then you have a timing problem.  Running the macro while it is being pushed onto the stack will cause the decisions on what code to generate to be made before the code that was setting the values that drive the decision would have had a chance to run. So the %NRSTR() prevents the timing issue (in addition to making the SAS log easier to read).

The FACTORY reference in the code is to the variable in the data set.  You just need a list of all of the factory workbooks you want to create.  So in this example I just used the ALL dataset your code was already using.  The BY and subsetting if will insure that each value of FACTORY is only used once to generate a macro call.

[Reeza]

I made an example of this recently for someone to split a report to multiple Excel sheets. If your reports are different for each sheet this doesn't work. First, get it working for one workbook then you wrap it in a macro and call it for each region.

Fully worked example
https://gist.github.com/statgeek/84cdf62f1b3ddbec471415de1fa65205

Tutorial on converting a program to a macro
https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[PhilC]

I would use By statement with a factory-site variable.  Avoid multiple datasets, made per site.  When you add or remove factories, its much easier to handle changes.

The ODSOUT object is cool.  I've never seen it used.  You have a leg up.  Proc PRINT or proc REPORT would leave you with blank pages if no records existed in the data set.  I have a QA report that I could write like this. 

By statement all the way

[Reeza]

And an Example of using BY with ODS EXCEL automatic splitting:
https://gist.github.com/statgeek/250462887998a9591972ae3a7baaeea3

[PhilC]

On that QA report I mentioned,  I use ODS DOCUMENT and output all of my reports for each site to one ODS DOCUMENT file.  When I create my separate Excel files I use ODS Excel and call my reports using PROC DOCUMENT.  Six of one, half a dozen of the other.  But it helps organize the code so code generating the report is next to the code generating the  report data.  The code generating the site reports can happen at after all of that.