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.