Using SAS code in other programs

Journalists can leverage SAS programs from government agencies to create lookup tables and translation tools in more accessible programs like Excel, Access, and FoxPro, enabling data analysis without requiring SAS software.

By Jeff Porter, IRE and NICAR

SAS out of your budget? Think again. You can use some SAS files with the more widely used programs such as Excel, Access or FoxPro to ease your work considerably.

SAS programs abound in state and federal agencies, and are often downloadable from the Web. They're valuable even for non-SAS users.

Using language in SAS programs - ending with the sas suffix and easily read with Notepad or any text editor - you can create lookup tables or UPDATE queries in Access or REPLACE commands in FoxPro.

The IRE and NICAR Database Library recently did some analysis for a project on the causes of death for the Columbia Missourian newspaper using a mortality file containing data of every death in Missouri during a 10-year period. A SAS program provided by health department authorities in Kansas helped us write a FoxPro program to categorize causes of death.

The official codes of causes of death, called the International Classification of Diseases, have changed over time. So the data has a different set of codes for deaths 1999 and after, and those before then. Those classification codes are extremely detailed. For example, there are several codes that represent a death caused by a faulty heart valve. Depending on what you need to know, that might be helpful. For the purposes of the Missourian project, though, we needed fewer, broader categories. For example, we wanted a "heart disease" category that would cover those heart valves and several other causes. And we preferred to have causes of death reported in plain English.

Fortunately, Kansas has a SAS program to do that.

Here's a snippet: proc format library = death print; VALUE $TIR31GP 20044,M20051V1001 /* TUBERCULOSIS */ 2009'='1002' /* SEPTICEMIA */ 2010/='1003 /* SYPHILIS */ 2015'=*1004 /* AIDS/HIV */ '2001'-'2003,'2006' '2008','2011'-'2014 2016'.2017'='1005" /* OTHER INFECTIONS-PARASITES */ 20181-02040'='1006 /* CANCER */ '2043' -''1007 /* DIABETES */ 2048'='1008' /* ALZHEIMERS DISEASE */ 2049 '2059'='1009 /* HEART DISEASE */ 2060'="1010" /* HYPERTENSION */ '2061' -''1011' /* STROKE */ 2062'='1012 /* ATHEROSCLEROSIS */

This part of the program takes deaths from several categories and aggregates them. For example, "2049" through "2059" are grouped together into another code: "1009." The text inside of the slashes are notes that are not part of the SAS program.

A couple of preparations, then we're off to write some simple lines of SQL. We need two additional fields in the Missouri death data. One will include the new codes such as "1009." Let's call it "AGGCOD." (Stands for aggregated cause of death.) The other will include English translations of those codes, also courtesy of the SAS program borrowed from Kansas. We'll call that field "TRANCOD." The field that already includes the range of codes we're going to aggregate is called "ALLCOD."

Now, with a lowly word processor or Excel, you can write commands to FoxPro.

Example: Copy and paste a section into a Word document, then on the tool bar, activate the button that looks like a paragraph mark. It allows you to view hidden characters. In this SAS file, we noted that the line before "OTHER INFECTIONS-PARASITES" has an extra paragraph return. We deleted it. Also, some, but not all, of the category notes were preceded by two tabs before the first slash mark. We inserted those to make all of them the same.

Then, we copied that into an Excel spreadsheet, then used Data > Text to Columns to make a new column, using the equal sign as the delimiter. Now, we have the new codes in a separate column. We made the appropriate number of columns between the old codes and the new ones - by simply counting the commas - and used Text to Columns again to delimit the columns with commas. Now, we have single codes or a range of codes in separate columns.

Rather than go through old ground we'll touch on just a couple of items regarding a function called BETWEEN. (MaryJo Sylwester, of USA Today, wrote an article for the September/October 2002 Uplink showing how to use Excel to write FoxPro program language.) Notice in several categories there are ranges of codes. For example, the "OTHER INFECTIONS" codes include "2001"-"2003." We'll do a simple find-and-replace in Excel to change those hyphens to commas, then we're ready to write a program line to throw in a BETWEEN function.

In our example, the codes "2001","2003" are in cell A2. The codes we eventually want to populate the just-created data field called AGGCOD are in cell C2. So in the cell B2, we'll type in this language:

=IF(MID(A2,7,1)=",","REPLACE all AGGCOD with "&TRIM(C2)&" for BETWEEN(ALLCOD,"&A2&")",""

In other words, if there's a comma separating the two items - that is, if there's a range in the particular cell and not a single value - then plug in the language needed. If not, then leave the cell blank.

The result: REPLACE all AGGCOD with "1005" for BETWEEN(ALLCOD,"2001","2003")

That line tells FoxPro to go to the new field AGGCOD and plug in the code "1005" every time the field ALLCOD values fall between "2001" and "2003." We just repeated these steps a few more times, resulting in a long list of SQL commands.

Then we were ready to move on to the next step, again from a short example of the Kansas SAS file:

proc format library = death print; VALUE STIR31DS 1001r='TUBERCULOSIS' 1002r='SEPTICEMIA' 1003r='SYPHILIS' '1004'='AIDS/HIV' '1005'='OTHER INFECTIONS-PARASITES' 1006'='CANCER' '1007'='DIABETES' '1008'='ALZHEIMERS DISEASE' '1009'='HEART DISEASE' '1010'='HYPERTENSION' '1011'='STROKE' '1012'='ATHEROSCLEROSIS'

The first codes, of course, are in the AGGCOD field. The rest of the language will eventually wind up in the field called TRANCOD. Again using Excel, with the equal sign as the delimiter, the spreadsheet software becomes a tool of writing SQL commands. No BETWEEN function is needed this time; it's a straight translation for one code to a text label. Assuming that the codes are in A column and the terms are in C column, in B column, the Excel language would be:

="REPLACE all TRANCOD with "&TRIM(C2)&" for AGGCOD = "&A2

The resulting SQL line: REPLACE all TRANCOD with 'TUBERCULOSIS' for AGGCOD = 1001

If one wanted to do the same work in Access SQL language, it would be slightly different. The Excel formula looks like:

="UPDATE deaths SET TRANCOD ="&TRIM(C6)&" WHERE AGGCOD = "&A6

The results:

UPDATE deaths SET TRANCOD = TUBERCULOSIS WHERE AGGCOD = '1001'

To download the FoxPro program created from the SAS file, go to www.nicar.org/techtips.html. There are even more techniques for using SAS materials without the SAS price tag. We'll share them in a future edition of Uplink.

Contact Jeff Porter by e-mail at jeff@nicar.org