topic What is an efficient way to create multiple excel spreadsheets with different tabs in SAS Programming

What is an efficient way to create multiple excel spreadsheets with different tabs

LL5 — Thu, 02 Jul 2020 17:38:53 GMT

I have 6 tables that contains different information (price, region, sales, manger, etc) for all inventory items.

I need to create excel spreadsheet for each inventory item. This excel spreadsheet would have 6 separate tabs to show each item's info from the corresponding tables.

Below is what I am doing (also see codes below)

Assign XLSX libname to create an excel file for each Inventory item
Use Macro A and B to create spreadsheet with 6 separate tabs for inventory item z3597 and J64x9, respectively

My question is since I have around 25 inventory items (and this number may go up or down each weekly), I would need to assign 25 XLSX libname and run the same macro for 25 times for each inventory number.

What is a more efficient way or better way to accomplish this task? Thanks for everyone's advices.

LIBNAME A XLSX '/sharedspace/inventory/users/sammy/file_one.XLSX';
LIBNAME B XLSX '/sharedspace/inventory/users/sammy/file_two.XLSX';
LIBNAME Inv '/sharedspace/inventory/weekly/Inv';

%MACRO A(SHEET_NAME, TABLE);
DATA A.&SHEET_NAME;
SET Inv.&TABLE (WHERE=(Inventory_num EQ "Z35q7"));
RUN;
%MEND;

%A(INVENTORY_PRICE,INVENTORY_PRICE);
%A(INVENTORY_REGION,INVENTORY_REGION);
%A(INVENTORY_SALES,INVENTORY_SALES);
%A(INVENTORY_MANAGER,INVENTORY_MANAGER);
%A (INVENTORY_PRODUCT, INVENTORY_PRODUCT);
%A (INVENTORY_TYPE,INVENTORY_TYPE );

%MACRO B(SHEET_NAME, TABLE);
DATA B.&SHEET_NAME;
SET Inv.&TABLE (WHERE=(Inventory_num EQ "J64x9"));
RUN;
%MEND;
%B(INVENTORY_PRICE,INVENTORY_PRICE);
%B(INVENTORY_REGION,INVENTORY_REGION);
%B(INVENTORY_SALES,INVENTORY_SALES);
%B(INVENTORY_MANAGER,INVENTORY_MANAGER);
%B (INVENTORY_PRODUCT, INVENTORY_PRODUCT);
%B (INVENTORY_TYPE,INVENTORY_TYPE );

Re: What is an efficient way to create multiple excel spreadsheets with different tabs

Reeza — Thu, 02 Jul 2020 18:33:52 GMT

CALL EXECUTE()

Use call execute to dynamically call your macro for a list of variables/criteria that you can generate.

Re: What is an efficient way to create multiple excel spreadsheets with different tabs

LL5 — Thu, 02 Jul 2020 21:09:48 GMT

Thanks Reeza, can you give me a quick example?

Re: What is an efficient way to create multiple excel spreadsheets with different tabs

Reeza — Thu, 02 Jul 2020 21:43:00 GMT

Call Execute specifically:

https://gist.github.com/statgeek/beb97b1c6d4517dde3b2

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

Re: What is an efficient way to create multiple excel spreadsheets with different tabs

s_lassen — Fri, 03 Jul 2020 09:09:36 GMT

Rather than using CALL EXECUTE, I normally write a program to a temporary file and %INCLUDE that. Mostly because the development is easier:

You can see all the code you have generated before executing
You can test a few of the generated steps before committing the whole batch
When you are done testing, you can add the %INCLUDE statement to your program

So assuming that you have the inventory items in a table called INVENTORY, you can do something like this:

filename tempsas temp;

data _null_;
  set inventory;
  file tempsas;  
  put
     '%A(' item ',' item ');' /
     '%B(' item ',' item ');' /
     ;
run;

%include tempsas /source2;

But as I said, you may want to take a look at the generated code and test a couple of statements before adding the %INCLUDE statement to your program.

Re: What is an efficient way to create multiple excel spreadsheets with different tabs

Reeza — Fri, 03 Jul 2020 15:28:27 GMT

@s_lassen wrote:

Rather than using CALL EXECUTE, I normally write a program to a temporary file and %INCLUDE that. Mostly because the development is easier:

You can see all the code you have generated before executing

I create my strings to pass to call execute in a variable STR that I can check before executing as well.

You can test a few of the generated steps before committing the whole batch

You can do that by limiting the obs in the input data set using the OBS= option to test it for a small portion of your code.

When you are done testing, you can add the %INCLUDE statement to your program

And then I uncomment the CALL EXECUTE() sections so that they run all at once.

I think both approaches are almost equivalent today. One plus to the %INCLUDE method though is that you do get a record of what was submitted that's easier to debug than going through the log of a CALL EXECUTE() generated program.

Re: What is an efficient way to create multiple excel spreadsheets with different tabs

Tom — Fri, 03 Jul 2020 15:50:37 GMT

The big advantages of writing the code to a file instead of CALL EXECUTE.

1) Macro timing. When using CALL EXECUTE to run macros that do code generation based on macro variables created in the macro you have to wrap the macro call in %NRSTR() to avoid timing issues.

2) The power of the PUT statement. You can use formats, VAR=.

3) The code can be formatted for human readability.

4) Same logic can then been used to generate code files that used in independent jobs. For example: Run your code generation step when the metadata changes and then schedule the generated code to run whenever the data changes.

The only advantage of CALL EXECUTE is that it is fewer steps. So good for quick code generation that is not too complex.