Help Needed: Automating Report Generation in SAS with Macros and Conditional Logic

[KelvinG]

Hello Everyone,

I'm working on automating a report generation process that involves data extraction, transformation, and conditional output formatting, but I'm still learning SAS programming. I've been experimenting with SAS Macros, but I'm having some trouble and would appreciate some advice from this experienced group.

This is my situation:

Data Sources: Several datasets kept in various libraries provide the data. Although the structure of each dataset is similar, department-specific variables may be included.
Condition: The report format needs to be slightly modified based on the department, which is a crucial variable in the datasets. For instance, whereas some departments require summary visuals, others require detailed tables.
Output: Each department's report should be saved in a separate folder, and the final output should contain both PDF and Excel files.

Problems I'm Having:

Macros that are dynamic: I'm having trouble adding conditional logic for various output formats to the macro I've started writing that loops through the department names.
Visual Exporting: Even though I know how to use PROC SGPLOT to create graphs, I'm not sure how to combine them with tables in a single PDF.
Folder management: Using department names to automate the creation and saving of reports into designated directories.

Questions for the Public:

• How should macros be organized for a report this dynamic?
• Are there any particular steps or settings in SAS that make exporting tables and graphics together easier?
• Does anyone have any examples of successful conditional formatting in macros?

I would be very grateful for any resources, sample code, or advice. Thanks in advance for your help and support.

Regards

Kelvinccsp

[Kurt_Bremser]

Step 1: create non-macro code for at least two departments.

Step 2: identify differences and similarities.

Step 3: replace department-specific code parts (e.g. variable lists) with macro variables, and wrap department-specific steps in %IF-%THEN %DO-%END

Step 4: decide how department-specific information to create macro variables are best supplied to the code

[PaigeMiller]

I agree completely with @Kurt_Bremser and his step 1 is so important, I'm going to repeat it with a slight modification in italics

create non-macro code that works for at least two departments

Why? Because if you can't get the reports to work without macros, you will never get the reports to work with macros. Many people ignore this advice. Don't be one of those people.

Macros that are dynamic: I'm having trouble adding conditional logic for various output formats to the macro I've started writing that loops through the department names.

Does not %IF-%THEN %DO-%END provide the logic needed here?

Visual Exporting: Even though I know how to use PROC SGPLOT to create graphs, I'm not sure how to combine them with tables in a single PDF.

ODS PDF does this for you.

Folder management: Using department names to automate the creation and saving of reports into designated directories.

Macro variable with appropriate folder names ought to do this. Use that macro variable in the ODS PDF command.

• How should macros be organized for a report this dynamic?

Organized? Each macro could be in its own .SAS file with appropriate file name to identify the macro.

• Are there any particular steps or settings in SAS that make exporting tables and graphics together easier?

The ODS PDF command has everything you need. It's pretty simple really. use the ODS PDF command before the first table or graph, and then ODS PDF CLOSE after the last table or graph.

• Does anyone have any examples of successful conditional formatting in macros?

Conditional formatting hasn't been mentioned before in your problem statement, but this is easily done via PROC REPORT or PROC TABULATE. It doesn't have to be a function of macros.

[Quentin]

Agree with the advice from Kurt and Paige. 

It's really important to separate SAS programming questions (e.g. how do I output a PDF that has a table and a graph in it) from macro programming questions (how do I automate generation of code to make multiple PDF reports...).

So first you write the SAS code (no macros!) to make two reports.  And in doing so, you may hit challenges, and you can post questions about the SAS programming challenges.

Then after you have working SAS code, you can work on writing a macro that will generate the working SAS code.  Then when you hit macro language challenges, you can post questions about the macro language.  And in those questions, you can show the working SAS code you are trying to generate, and also show the non-working macro.  That will make it easier for people to understand the intent of the macro, and help with resolving macro language issues.

Also, if you're still learning the SAS language, it may be too soon for you to try learning the macro language.  The macro language is a completely separate language.  I often recommend to beginners that they program in SAS for a year or two, until the point that they are really confident that they understand the SAS language, and also understand the purpose of the macro language, before they start learning to write macros.

[Cynthia_sas]

Hi:
And, for a concrete example, this paper https://support.sas.com/resources/papers/proceedings13/120-2013.pdf walks you through the process of starting with a working program and eventually converting it into a macro program. I think it illustrates the process that we are all recommending you follow.
Cynthia

[Reeza]

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

Here's a tutorial on this, I wrote a while back 🙂