Best technology for 4 types of pains in report programming

[Bravez]

Hi all,

(Edit in Italic based on the discussion. Title also updated.)

Not sure how many of you need to do report programming. But I am curious whether any of you feel the same pains like I got (at least for healthcare) in tedious report programming, including complex table building, template programming, manual edits, like below list across efficiency, flexibility, extendibility, and easiness to learn & use?

Like the screenshot below with a simple example of report with text, table, chart, figure (not to talk about batch listings of dynamic table/ variable rows), other than generating the analysis results (the fun part), many lateral activities and various pains to make it from template design to the final report...

A. Efficiency:

1. have to program headers/ labels/ footnotes to build complex tables, 
2. have to transpose the text/num variables carefully so that grouping/ result values do not mess up.
3. have to calculate carefully the width/height of table row/ column/ cells/ merged cells during programming,
4. have to care about headers/ footers/ margins of the template layout 
5. have to implement company themes/ styles/ layouts (color, font, size, logos, etc.) by programming
6. a lot of iterative efforts (debug, QC, modify, etc.) simply due to above activities... 

B. Flexibility:

1. Due to the pains in programming efficiency, we limit our flexibility to wrap some tedious work in a complex macro. 
2. Or once the hard-code was done (build, debug, QC...), often we are very reluctant to make further edits (e.g. change requested by stakeholders) to avoid the iterative cycles.
3. Every now and then we have to give up some great ideas/ ways to present the results and insights, due to the limited flexibility of established workflows.

C. Extendibility:

1. Most of our established workflows are highly customized to only a specific programmer/ team/ platform, while many organizations have multiple programmers/ teams/ platforms. The work is not scalable (in terms of jobs-to-do, not computing power). 
2. Most of our established workflows are highly customized to a single file format (e.g. rtf/ pdf), no extendibility to others (e.g. xlsx/ pptx/ docx for the same set of results). If we want to have multiple formats, we need to amplify the efforts. 
3. For federated studies, or cross-database collaborations, usually each team use their own way to generate the results, and the final summary report is a painful manual copy-pasting process.

D. Easiness to learn & use:

1. the endless details in training to use the different generic reporting proc statements... 
2. for the self-built complex macros, still a lot of details to learn and use...
3. A lot of mental burden (search, learn, remember, and tweak...) on the lateral activities and details like template programming.

Do you have the same or similar pains here? Or you also have further pains in reporting?

So far, my personal favorite technology is the DDE engine but it only works for Excel/Word and it's fading away...  I like DDE not because of the technology itself, but because of the general benefits that I gain across efficiency, flexibility, and easiness to learn & use, so that to finish my different reporting tasks. 

Based on below discussion, please allow me to reword my puzzle:

In considering to do many different and evolving reporting tasks in different industries, other than (painfully) building a complex macro for every new task, do we have some "best general technologies" similar or better than DDE, that can maximize the benefits across efficiency (e.g. minimal tedious lateral activities), flexibility (e.g. allow changes easily), extendibility (e.g. allow different platforms/ report formats), and easiness to learn & use (e.g. no painful training of tons of evil details)? 

It will be great to have your comments. Thanks a lot. 

[ballardw]

Boiler plate text such as that intro paragraph is easy with Proc Odstext. Place the values in data set with each value in separate variable.

data example;
   value1="text for value 1";
   anumber= 123;
   anothernumber = 345.67;
   Diseasename ="MacGregor's Syndrome";
;

proc odstext data=example;
p cat("An example of basic boilerplate showing ",value1," followed by more text. 
Continuing to display number values ",anumber," and the associated ",anothernumber,".");
p;
p "There are exceptions for "||diseasename;

run;

Devil in some details.

You don't mention which reporting procedures you need or even what your data look likes. If you are working from some description that is specifying elements it might be that the data step Report Writing Interface is of use as you control every cell to apply specify formatting.

Since SAS output can be sent to Excel, RTF (the Word destination being somewhat experimental) and Powerpoint I an not sure what your request of "Do you have any better techniques to solve above problems, in Excel, Word, PowerPoint?" actually is requesting.

Analyzing requirements and finding the common elements would be one of the first steps as those are the things that can be relatively automated, possibly with formats or macro variables or macro coding.

Pretty much any reporting process has such a cycle. Most software has somethings that are easy to do, some things harder.

[Bravez]

Thank you, ballardw. It's another learn.

It seems generally we have to generate a specific new dataset (either simple or complex) on top of the analysis result dataset, so that to make use of the proc odstext/ proc excel/ proc rtf/ proc template, etc.. Let alone the pains in learning and building of the detailed evils in the output proc process codes here... 

Could we simply calculate the results for the table, and use the same values to inject to the paragraph, without programming it? For filling the table in this Word example, could we simply inject values directly into the table like DDE without coding the headers/ labels/ boarder/ background color, etc.? 

Other than generating the analysis results (only results, not even a whole table dataset with headers/labels matching the table template...), I would kind of consider all other activities are lateral and tedious. Yet we have to make the best and professional looking report for different stakeholder needs, e.g. senior management and regulatory submission. What's your best way to minimize such lateral activities? 

[ballardw]

"Best Practices" typically start with clear descriptions of requirements. Get one bit working with management acceptance and then add the next. Then provide a budget of the impact of changing things after there is an approved approach for a specific task. It is amazing how often you can get management to accept a minor difference from the initial desired specification if you can show them something like 90% or more and then say that remaining 5% will require the budget of doing the entire first 90%

Without specifics hard to provide any details. Here is a link to one approach to generalizing a specific form of demographic tables that may provide some some clues: https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-...

The link above includes links to a couple of program files and additional references. Much of the table that you showed is possible, I think, with code from that. However it may require modifying your existing data.

Probably going to be easier to create a summary data set to provide the text in a boiler plate than to attempt to use the full data.

A "standard" graph type package would be similar.

SAS provides one tool called Proc Document that specifically designed to re-arrange previously created output into different order, which could be quite helpful.

[Bravez]

Thank you @ ballardw  for pointing me to that post. It's impressive that @ JeffMeyers has greatly increased the efficiency of their workstreams (i.e., their preferred way of showing demographics) by wrapping up a lot of tedious activities in one big complex macro.

This just reminds me of the tradeoffs between efficiency and flexibility (see the screenshot). If more dimensions are to be considered in the tradeoffs, then I think probably the extendibility and easiness to learn & use should be included. Dimensions like security are not considered here since as a reporting programmer we usually only focus on how to easily finish all our various reporting tasks and collaborations. With the standard technology, to gain high efficiency, we give up substantial flexibility (like specialized dashboards or Jeff's macro above); to gain high flexibility, we lose substantial efficiency (like using generic SAS or R programming); most existing single platform dependent internal/external packages are in between, moving along the curve.

The rectangle shaded area indicates the benefits for the users, probably it should be four dimensional (efficiency, flexibility, extendibility, and easiness to learn & use). I am in favor of DDE because it is a leap on top of standard single platform dependent reporting technologies and it probably maximizes the overall benefits of at least three dimensions (except extendibility) across all our different reporting tasks, i.e. not constrained to build only a demographic table allowing only a few specific statistics or layouts, a more "generally" good technology. 

In this sense, please allow me to reword my question: in considering to do many different and evolving reporting tasks in different industries, other than (painfully) building a complex macro for every new task, do we have some "best technologies" similar or better than DDE, that can maximize the benefits across efficiency (e.g. minimal tedious lateral activities), flexibility (e.g. allow changes easily), extendibility (e.g. allow different platforms/ report formats), and easiness to learn & use (e.g. no painful training of tons of evil details)? 

[Bravez]

After some more thoughts, I've updated the above graph like below.  The overheads of low efficiency, flexibility, extendibility, and easiness to learn & use, as well as the fragmentation itself, in the end will all translate into high costs.