Edit mainframe SAS data in Excel through Attachmate Extra

SAS as a programming language is one of the most powerful in the world. It's data however, remains locked within SAS datasets until a program can be written which exports them to another format. If the data is held on a mainframe (as most of the best applications of SAS are), then the user interface, through the operating system, is likely to be unappealing and low in features. It's very likely that the user is more familiar with the user interface in other regular desktop tools, and would prefer to edit the data using that package.

I found this out very quickly whilst working on a project with the worlds largest on-line grocery retailer Tesco .com. The project was to set up staffing targets and a payroll budget model for the staff involved in the operation. The users of the system were productivity analysts, managers, and accountants all of whom had regularly used MS/Excel, but were much less familiar with the user interface of the mainframe, and the nuances of using the mainframe through SAS. 

The requirements of the project were far reaching, but required a flexible, low maintenance and feature rich system. Whilst the system had requirements specific to calculations involved in setting staffing levels, and payroll budgets, it was clear that these rules could change in many ways, and that this level of maintenance should be capable by the users rather than a programmer. To meet these goals, whilst meeting a tight deadline imposed by the budget timetable, I developed a system which used many pre-built SAS procedures, such as FS/Edit, and FS/View. Both of these tools allow data of any structure to be viewed or editted, and provide tools for carrying out regular tasks such as limiting the data shown to specific categories, finding data, scrolling through records, etc... However, the user interface was unfamiliar to the user who was more used to a windows based approach with scroll bars, and point and click menu systems, rather than a command line based approach.  This introduced a further learning curve for the user, and slowed down their use of the system.

I could have manipulated the system to provide many of those capabilities, but it was also clear that Excel was being used extensively to prepare input data, and to present and manipulate the results. I therefore decided to use Excel as a partner to mainframe SAS through the user interface Attachmate Extra using VBA.

This task involves many challanges ;

1. Getting SAS data of any format into a format which can be read by Excel.
   Excel supports many input formats. SAS datasets are not one of them. SAS supports an Export procedure for creating data of different formats, but not on the mainframe's MVS operating system. The dataset may include any number of variables of different names, types, and lengths. 
2. Transferring the data to the PC.
3. Opening Excel, and importing the data.
4. Transferring the data back to the mainframe.
5. Re-importing the data, and supporting changes to the structure of the data (e.g. added and deleted variables).
6. Do all of this with the minimum of user intervention.
   

1. to develop a user interface using the SAS Application Facility (SAS/AF) to allow the user to choose the data they want,
2. add a SAS program which would export a dataset to a tab delimited text file (so that data with embedded commas such as addresses could easily be supported)
3. create a point and click option which would transfer the data to the users PC and open it in Excel, using VBA through the mainframe emulator Attachmate Extra!
4. leave a message on the PC asking if the user wants to keep the changes made and adopt them on the mainframe file, waiting for the user to click yes or no before proceeding with the VBA macro.
5. If yes was clicked, transfer the file back to it's original mainframe location.
6. Finish the VBA macro by sending keystrokes to the mainframe which instigate the next SAS step ;
7. set up a SAS program which will re-import the data on the mainframe.

Screen shots of this process are shown below ;



The above screen shows the user selecting the type of data they want, subsequent similar pop up boxes allow them to select an individual SAS dataset, once selected, the data is exported to tab delimited text file with variable names as column headings and the user is invited to click on a button on a "quick pad" (a cusomisable menu bar) to instigate the VBA process.



(Here the quick pad has been moved to float over the screen - generally it floats over to the left of the screen).

When the quick pad is clicked, the VBA process starts, and the text file is downloaded to the PC. The process followed is ;

1. The data exported always goes to the same location prefixed with the user id of the user.
2. The SAS screen is switched to a host command processing screen (called TSO Submode) without leaving the application screen. 
3. The data exported is transferred to a fixed location on the PC, with a .txt suffix indicating that it is a tab delimited text file. 
4. Excel is started (even if an instance of Excel is already open, a new one is started), and the downloaded file opened. Excel automatically recongises that it is a tab delimited file from the suffix ".txt". It then opens the file, with each column recognised from the tab delimiters.
5. A message box is displayed, with the question "Are you happy to replace the mainframe file ?". The macro waits for a response.
6. The user uses Excel to process the file, saving the file and closing Excel when happy with the changes.
7. If the user clicks yes to the question in step 5, the file is loaded back up to the mainframe.
8. The mainframe is taken out of TSO submode, and switched back to SAS.
9. Keystrokes are sent to the SAS screen which tell SAS to re-import the text file, and the VBA macro finishes.
10. The SAS screen runs a SAS program which reads the text file (updated or not), examining the column headings, delimiters, and contents to ensure that the same number of delimiters exist on each row. Once it has established that the import file is valid, it build sas statements (using 4GL programming) which defines a new file using the original variable attributes plus any new attributes it has identified, and reads in the file.
11. If the import process is successful, it saves the imported file, and informs the user of any deleted or added rows and / or columns.