Generate Reports based on Dynamic listing of tables

[TylerP8813]

I have written a program that looks at values in a column from dataset Test that dynamically creates resultant datasets based on the values in the column and the number of records that contain that value. 

For instance, the values that could occur in that field would be A, B, C, and D. Five records have "A" in that field, two have "B", three have "C", none have "D". So, when I run the program i get table A with 5 records, table B with two records, table C with 3 records, and no table D. When I run it next quarter, that could change. There could be tables B,C, or D and no A. All of the resulting tables are stored as sas7bdat in there own library.

Now, I want to build a functionality that queries the library and applies a report template or two (preferably ODS; some of the values A,B,C, or D may have different requirements for how their report has to look) and creates a report for each of the tables present in the library without having to hardcode the names of the tables (rather than just the 4 in the example, there could possibly be 100 or more tables and the total could change quarter to quarter).

[Reeza]

Sorry, I don't see a question there. Which part do you need help with? It would likely help to include some sketches of your code or a simplified example.

[TylerP8813]

Attempted to clarify below

[Tom]

It is really not clear what you are asking. The beginning of your question made it sound like you were just splitting a dataset into multiple smaller datasets using the value of a variable both to define how to split and also how to name the resulting data.  That is a frequently asked question here and other forums.

The second part sounds different. Instead if sounds like you have a set of define programs and you want to run only the programs that are mentioned in the data.  Again that is easy, but you need to have defined the programs before their names appear in the data.  You could possibly handle that by having a generic report that you use for unexpected names that appear in the data.

[TylerP8813]

To clarify,

My question is:
1. Is what I lay out in the final paragraph possible, given that the tables in the directory will not always be the same or have the same name quarter to quarter?

2. If the answer to 1 is "yes", could I create a couple stored report templates and apply them via, say, ODS to print out the reports?

3. If all of that is "yes", could someone point me in the right direction as I am not sure how to phrase the question in a directory search to adequately get the answers I'm looking for if someone has already addressed this?

[Reeza]

It depends on what you mean by 'report templates'. Can you expand on that?

Also, what output format do you need reports, PDF, Excel, Word, PowerPoint?

For each table,is it because you separated them into individual groups? If so, that sort of removes some of the good ways to do that using BY groups or WHERE statements instead.

Reports can be incredibly complicated to very simple.

EDIT: here's an example of one of the more complicated reports I generated using SAS, which includes dynamic text. I don't recommend doing this though - it's a lot of work to get that text right. It splits out by geography so 132 reports are generated.

[TylerP8813]

The format would likely need to be PDF or Excel. That's why I asked about ODS. For all intents and purposes, these will be invoices that are generated. So, the majority of the information would be the same but certain fields would need to be changed per the company being invoiced (e.g., contact info, company name, etc.) but I have all of that information stored in a table, so I could just get it from there. Plus, some of the companies want the info presented a little differently. For instance, one company may just want a total for each of the individual items, while another wants all the purchases listed line-item style.

I currently have the program set up to create all of those individual tables in order to retain the line-item data related to the individual manufacturers for records retention purposes. If it would be easier to just run the original whole dataset through the invoice generation process and use BY and WHERE statements to determine which invoice format gets applied, etc. I'm totally open to that. I'm just not very familiar or experienced with generating reports, etc. beyond the more basic functionality of "create this one report from this one table using this standard format".

[Tom · Accepted solution]

That sounds like a much easier problem than your original question.

Basically you need to create a macro that produces the invoices that has all of the complexity about how to produce them based on the company (or other attributes of the data).

Then you just use the data to generate the calls to the macro.

So if the input is just one dataset with a variable like COMPANY that can be used to drive the report then you might just need something as simple as:

filename code temp;
data _null_;
  set current_data;
  by company;
  if first.company then put '%produce_invoices(' company= ')';
run;
%include code / source2;

If instead the driver for what invoices to produce is a list of files in a directory then just add a step in front that converts the filenames into a dataset.  There are lots of examples of how to do that.

[TylerP8813]

I used a similar step to generate the resulting tables i mentioned before. 

As I'm searching for examples to help guide me, is there any particular phrases that you would recommend so that I'm not endlessly digging through unrelated content?

[Reeza]

How much SAS do you know?

If you're just starting out, my recommendation would be to do the following. 

1. Make working code for each report template (ODS PDF, PROC PRINT, REPORT, TABULATE, MEANS)

2. Create a list/dataset that has specific companies and which report template they need to be using. This may be a manual process, ie an Excel file that you update and then SAS can read that in as part of the process or a datalines approach is fine. 

3. For each template, generalize so you have a specific macro function for that report

4. When you need to run, subset the list from #2 and then use logic to call the correct macro each time. Use CALL EXECUTE for this. 

There are some other ways, but I think this is easy enough for a beginner to follow and flexible enough to adapt to changes later on without too many issues.

You should definitely start with #1.

I think you're trying to develop something similar to what I did here, except all my reports are the same. You need to add the variation options.

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

HTH!

[TylerP8813]

Thank you Reeza!

[TylerP8813]

Reeza,

I followed your example and it seems to be working well. I do have a follow-up question if you don't mind. I am using ods pdf and am inserting titles and footnotes within the macro step. Are you familiar with a way to embed a value in a title similar to the way, for example, you might in a subquery in a proc sql step. 

For example:

title1 j=c "&Manufacturer.";
title2 j=left "STATE: Ohio" j=right "INVOICE NBR (optional): &Invoice_Num ";

In this example, "Manufacturer" takes the place of "r_make" from your GitHub example. I already have invoice number generated in the original table (the one that the TABULATE procedure in the macro will be pulling data from). I just need to know how to get the value into a title for the corresponding manufacturer.

[Reeza]

Add invoice number as a parameter in your macro and pass it from the CALL EXECUTE as well. Then your usage will be fine.

[TylerP8813]

Thanks Again!

[TylerP8813]

One more question if you don't mind:

I've added a couple other parameters to populate fields. Two of them (specifically related to character fields containing address info) cause me to receive 

"ERROR: All positional parameters must precede keyword parameters."

Relevant lines of code: 

%macro request(manufacturer=, invoice_num=,invoice_contact=,address_1=, address_2=);

data macro_call;
set manufacturers (obs=2);

str = catt('%request(manufacturer=', manufacturer,',invoice_num=',invoice_num, ',invoice_contact=',invoice_contact,',address_1=',address_1,',address_2=',address_2,');');

call execute(str);

run;