Edit mainframe SAS data in Excel through Attachmate Extra
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.
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.
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.
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 ;
The solution was
- 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
- Transferring the data to the PC.
- Opening Excel, and importing the data.
- Transferring the data back to the mainframe.
- Re-importing the data, and supporting changes to the structure of the data (e.g. added and deleted variables).
- Do all of this with the minimum of user intervention.
- to develop a user interface using the SAS Application Facility (SAS/AF) to allow the user to choose the data they want,
- 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)
- 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!
- 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.
- If yes was clicked, transfer the file back to it's original mainframe location.
- Finish the VBA macro by sending keystrokes to the mainframe which instigate the next SAS step ;
- 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
(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 ;
If you are interested in using this tool, please contact us.
- The data exported always goes to the same location prefixed with the user id of the user.
- The SAS screen is
switched to a host command processing screen (called TSO Submode)
without leaving the application screen.
- 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.
- 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.
- A message box is displayed, with the question "Are you happy to replace the mainframe file ?". The macro waits for a response.
- The user uses Excel to process the file, saving the file and closing Excel when happy with the changes.
- If the user clicks yes to the question in step 5, the file is loaded back up to the mainframe.
- The mainframe is taken out of TSO submode, and switched back to SAS.
- Keystrokes are sent to the SAS screen which tell SAS to re-import the text file, and the VBA macro finishes.
- 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
- If the import process is
successful, it saves the imported file, and informs the user of any
deleted or added rows and / or columns.