SAS for Excel Jockeys

The ubiquity of Microsoft Excel and Word on desktop computers has made it a default entry point for many users to view and edit their information. Once Excel was included into the MS Office suite in 1993, it became the killer app overtaking other spreadsheet heavy weights such as Lotus 1-2-3. Although Excel has analysis capabilities, it does not have the powerful statistical procedures and depth of SAS. When analyzing certain types of data, such as financial information, Excel is the tool of choice. Its capabilities to easily generate graphs along with the visual pivot table provide powerful methods to view and analyze data. In certain cases, however, Excel is not capable of performing particular tasks which SAS can provide. Some of the topics that this paper will elaborate on include:

• Connecting SAS to Excel – The use of TCP port communication allows Excel to connect directly with a SAS session


• SAS Macro Management – Managing SAS macros that can be delivered to Excel users


• Deliver SAS Data – SAS Datasets can be delivered directly to Excel or Word in optimized smaller blocks


• Pivot Table and Graph – SAS data can be formulated and delivered to Excel users in the form of a Pivot Table and/or Graph (Excel Chart)

The union of SAS and Excel enables power users and decision makers, who may not be SAS programmers, to fully explore their business data with the full analytical power of the SAS system.

SAS and Excel
The advent of the modern spreadsheet has revolutionized how users analyze data such as financial data. It visually displays information in cells similar to how a professor may describe the information on a chalk board. The disadvantage of a chalk board is when a change or mistake is made. Rather than having to erase each item on the board, the cells of a spreadsheet can be updated through a formula. Programming languages have evolved to work with spreadsheets so they can provide a very efficient method for financial analysts to perform analysis interactively. There are many advantages to this approach but there are also some limitations. Some of these limitations include:

• Change Control – The changes to the spreadsheet are very interactive so the information stored previous to updates are lost. This can lead to regulatory compliance issues or limit the ability to easily roll back to an earlier version.


• Security – Spreadsheets are designed for individuals to work on their own set of data. They cannot easily handle multiple users with different permissions. This combined with the lack of change control makes it difficult to function as a secured system for large sets of data in a large organization with many users.


• Cell Based Formula – Formulas defined for a cell in Excel are defined for a particular cell. An example is to sum up all the cells by their identified cell row and column name, =SUM(A1:A3). In this example, the cells A1, A2 and A3 are summed to a specified new cell such as A4. This is limited to cell A4 and is not easily replicated in an array of multiple cells. There have been scripting languages to enhance the capabilities of formulas but it is still limited since formulas are designed as expressions and not designed for complex algorithms which require a full featured programming language.


• Multiple Users – Spreadsheets are designed for a single user. This creates limitations if multiple users need to update the same data.


• Statistical Analysis – Formulas are designed as an expression to derive at a numeric answer. There is some statistical analysis that can be applied through a spreadsheet but this approach is limited. It is not optimized for more complex statistical modeling such as performing a multi-variate regression analysis.

The exploratory and visual aspects of spreadsheets make them very suitable for certain types of financial calculations. However, some of the limitations mentioned above can prevent power users from performing data mining to truly model business conclusions that can be delivered by SAS. SAS has the powerful programming language including a library of statistical procedures which deliver to users functionality beyond the capabilities of spreadsheets. The two tools can function together symbionically to form a complete solution. This paper will explore the integration of SAS and Excel in ways that give SAS programmers methods of delivering to Excel jockeys the power of SAS without requiring them to program SAS.

complete paper is found at: SAS and Excel published paper , SAS Export to Excel, and SAS Programming.