Dynamic table numbering for proc report by output

[js5]

Hi,

thanks to help from this community I was able to generate quality .docx output using ods document, proc report and ods word. I am generating global titles and footnotes with title and footnote, Table titles with compute before _page_; line; endcomp; and TOC entries with contents= parameter of proc report:

proc sort data=sashelp.cars out=cars_sorted;
	by Origin;
run;

ods document name=input(write);
title "ACME Inc.";

proc report data=cars_sorted contents="Table x: Average MSRP for #byval1";
	by Origin;
	columns Origin Make Type, MSRP;

	compute before _page_;
		line1 = catx(" ", "Table x: Average MSRP for", Origin);
		line line1 $varying200.;
	endcomp;

	define Origin / group noprint;
	define Make / group;
	define Type / across;
	define MSRP / mean;
run;

ods document close;
ods word file="C:\users\&sysuserid\Downloads\SAS\table_numbers.docx"
	options(cant_split="no"
	toc_data="yes"
	toc_level="1"
	contents="yes");

proc document name=output(write);
	copy \work.input\Report#1\ByGroup1#1\Report#1 to ^;
	copy \work.input\Report#1\ByGroup2#1\Report#1 to ^;
	copy \work.input\Report#1\ByGroup3#1\Report#1 to ^;
	replay;
quit;

ods word close;

So far so good.

The issue I have now is how to automatically number the tables in cases where by processing is used and the individual by groups should be numbered on the top level. So first by group is table x, second is table x+1, third table x+2 and so forth. With the exception of the #byval, I can only give one title to all the tables.

I could of course replace by with where and do a macro loop or create a helper variable mapping 1:1 to the by variable and use it as a second by variable, but neither of those solution appear to be very clean. Is there a better way?

[Cynthia_sas · Accepted solution]

HI:

  Using a helper variable, but trying to avoid switching to a macro-based approach, this is what I came up with:

Of course, I didn't mess with the ODS WORD or the ODS DOCUMENT, because the point was to show the use of 2 BY variables that are essentially the same, one of them TABL_ORIG is just wordier than the other, ORIGIN. This also assumes that the table for Asia will always be numbered as Table 1 and Europe as Table 2, etc. If that's not the case, then the format has to change or you'll have to create the formats on the fly or you may need to move to a macro based approach in the end.

Hope this helps,

Cynthia

[js5]

This has worked perfectly, thanks! Additional advantage of not going with a macro loop with where is that each dataset is only being read once and not as many times as there are by groups.