Create and Send Mass Excel Reports Using REXX, SAS, HTML (XML)

Cours create and Send Mass Excel Reports Using REXX, SAS, HTML (XML), tutoriel & guide de travaux pratiques en pdf.

BUSINESS TASK EXAMPLE

A company has hundreds of territorial sales representatives and every day a specific territorial sales report showing product sales at a customer level has to be sent to each sales representative. Each report has to be sent via Email in an attached Excel formatted file. Sales quantities, territorial IDs and email addresses are stored in a huge invoice database together with other data. To avoid filtering multiple times that huge database, the sales, IDs and addresses should be extracted first and kept in two separate entities. So the sales for all territories will be extracted first and kept in a SAS data library, the territorial IDs and email addresses will be extracted and kept in a separate external file.
PROPOSED COMPUTING ARCHITECTURE
1. Operation System with installed REXX interpreter
2. REXX program, to invoke a reporting SAS program and Email system to send the emails with the attached reports 3. SAS program, to create an Excel report in HTML(MSO XML) format
4. Email system
5. Excel Here is the sequence of steps to create and send reports using the proposed mechanism applied to the above business task example. The REXX program reads a first record from the file with a particular territorial ID and a respective email address, and then invokes the SAS reporting program with the territorial ID as a parameter. The SAS reporting program extracts the sales quantities for this particular territorial ID and creates the report in HTML (MSO XML) format. The REXX program checks the SAS return code and based on that invokes the Email system to send an email with the attached report (emails could be sent also from SAS program). Then the REXX program reads the next record with the next territorial ID and a respective email address, and the executions of the SAS program and Email system repeat. All goes on until the last ID and address are read and the last executions are finished. After that all emails with the attached reports are sent and the task is completed.

REXX

REXX is a high level (script) language, it means can invoke other programs written in other languages. Each statement produces more executable code. It is interpreted (doesn’t require compilation, very good for testing) and it is called often a glue language because can join different components like OS commands, functions, routines or even GUI objects. REXX was first published in 1979 by Michael Cowlishaw from IBM. Since that time dozen of other versions have been created for different platforms and with different, extended features. The best known are: REGINA REXX (by Anders Christensen, multiplatform, 1992), REGINALD (by Jeff Glatt, for Windows, 2001) and others like NetREXX, REXX/imc, BREXX, roo.

HOW TO INVOKE SAS FROM REXX

The following REXX sample statement allows invoking a SAS program under mainframe MVS: address LINKMVS SASXALO “SYSPARM=TerrID”;. The following REGINA REXX sample statement allows invoking a MySASpgm from Widows XP: address SYSTEM sasexe ‘-sysin C:\MySASpgm.sas –icon –nosplash’; where sasexe=”’C:\ProgramFiles\SAS Institute\SAS\V8\sas.exe’”;.

HOW TO CODE HTML

One of the solutions to write a report in HTML format is to use the Output Delivery System. The other option is to use the SAS Base PUT statements and write HTML code directly into a report file. Of course ODS saves tedious coding of PUT statements but ODS is a “broker” in HTML coding and sometimes it can create too much metadata which then can take more time to display the report under Excel after clicking an email attachment.

HOW TO SEND EMAILS

The emails can by sent from a SAS program using a filename email statement. To send emails from mainframe we can invoke XMITIP command from REXX.

OPTIMIZATION PROBLEMS

Optimization efforts in such automation process are focused first of all on reduction of execution time (CPU). The SAS reporting program, which is going to be invoked multiple times, should run as fast as possible and also should produce as less as possible log messages. The HTML code used to format the Excel spreadsheet should be very precise. It means the HTML code should be short to reduce the SAS program execution and also to reduce time to display a report under Excel after clicking an Email attachment.