Mapping prescription drug patterns: An illustrated tutorial

Frank Bass, The Associated Press
Urban Health Journalism Workshop, New York, Oct. 13, 2007

Data is helpful, even essential. But often, it’s not enough.

Frequently, analysis is impossible without a visual representation of the data.

Take, for example, a recent Associated Press study of prescription painkillers.

For years, ugly reports had come out about a particularly potent pain medicine called OxyContin. It was so popular in Appalachia that it had earned the nickname “hillbilly heroin.” Aging cancer patients were selling pills to hollow-eyed addicts, who were robbing pharmacies and physician offices to support their habits.

There was very little doubt that the drug was being widely abused. In the midst of our reporting the story, OxyContin’s manufacturer, the Connecticut-based Purdue Pharma, would eventually plead guilty and pay a whopping $600 million fine for misleading patients and doctors about its addictiveness.

A search of the AP archive found hundreds, if not thousands, of brief stories about OxyContin abuse, or “diversion,” as federal drug enforcement authorities liked to call it. Not all had West Virginia datelines, and most went something like this:

LAYTON, Utah (AP) – A Layton man arrested after an armed robbery at a Layton Shopko pharmacy is being investigated in many of the recent OxyContin robberies in Davis and Weber counties.

My former editor, John Solomon (now at The Washington Post) and I began talking about the story in early summer 2006. We theorized that the use of OxyContin would be like any other abused drug: It would be developed somewhere, then spread. Like heroin in the 1970s or cocaine in the 1980s, OxyContin had to have an origin before spreading out. It was safe to say that Appalachia was the origin: Its population was older, and older people tended to be in more pain. But where had it gone since its introduction in 1996?

The Cleveland Plain Dealer had done an extraordinary (and, I thought, underappreciated) story in 2001 on Ritalin. Using Drug Enforcement Administration data, it had found rates varied widely around the country. I remembered seeing the story and wondering if usage patterns for other drugs could be developed using the DEA database.

The initial figures were culled from a DEA database with the very catchy name of Automation of Reports and Consolidated Orders System (ARCOS). The system gives retail sales figures – from hospitals, pharmacies and physician offices – by quarter, for each of the 800 three-digit ZIP codes in the United States. It consolidates the reports into PDF files, available from 1997 on, for a series of Schedule II drugs. State and federal investigators often use the data to analyze suspicious patterns.

We began in summer 2006 by downloading more than 3,000 pages of PDF report files from the DEA Web site.

The files looked like this:

[Fig.1 – Click for larger image]

The first order of business was to put the data into a readable format for analysis. Anyone who’s ever tried to convert a PDF file to any other format knows how time-consuming and painful it can be.

Take the above PDF file, copy it, and try pasting it into a spreadsheet. Here’s the result: [Fig.2 – Click for larger image]

It’s possible to use spreadsheet functions to assign columns to the rows and rows of numbers, but there are two issues: One, it would require a lot of manual work or advanced scripting to fix two-word geographic labels, such as “STATE TOTALS, “ and two, it could only be done one page at a time. Given that each page would have taken about 10 minutes, we were looking at an extraordinary amount of work to go through 3,000 pages.

For years, the Associated Press had used Monarch’s Redwing product. Again, though, it was good for one page at a time.

So we purchased a copy of PDF2XL, a product from CogniView, which allowed us to set templates for the columns and convert hundreds of pages in one fell swoop.

The program looked like this:

[Fig.3 – Click for larger image]

We didn’t expect to use Excel for the analysis but decided that it would serve a useful purpose to create sets of uniform files that we could put into SAS for the heavy lifting. Given that we would need a relational database product to join the three-digit ZIP codes to population figures before transferring it to a mapping program, we used the spreadsheet as an intermediate step.

The output looked like this: [Fig.4 – Click for larger image]

I wrote a Visual Basic macro that cleaned up the data nicely, putting the name of the drug in a new column, eliminating extraneous blanks and quarterly data, and getting rid of drugs (such as buprenorphine) that we didn’t feel we needed. Ultimately, we decided to expand the scope of the story to include the five most commonly used painkillers by weight: oxycodone, hydrocodone, morphine, codeine and meperidine.

A finished spreadsheet for one substance looked something like this: [Fig.5 – Click for larger image]

Of course, we had five substances for each state and the District of Columbia, so that amounted to 255 spreadsheets for each year. And since we had data for eight years, we churned out more than 2,000 spreadsheets.

Our next step was to consolidate the spreadsheets. We could have written a script to pull them into another series of spreadsheets, or even a database. But we wanted to get the numbers into a single file as quickly as possible for a nationwide analysis. First, we imported the data with the 2005 figures for each state into SAS:

%macro dostate(st);
PROC IMPORT OUT= PAIN.&st._OXY05
DATAFILE= "J:Painkillers&st._oxy05.xls"
DBMS=EXCEL2000 REPLACE;
GETNAMES=YES;
RUN;
%mend dostate;
%dostate(AK);
%dostate(AL);
%dostate(AZ);
%dostate(AR);
%dostate(CA);
%dostate(CO);
%dostate(CT);
%dostate(DC);
%dostate(DE);
%dostate(FL);
%dostate(GA);
%dostate(HI);
%dostate(IA);
%dostate(ID);
%dostate(IL);
%dostate(IN);
%dostate(KY);
%dostate(KS);
%dostate(LA);
%dostate(MA);
%dostate(MD);
%dostate(ME);
%dostate(MI);
%dostate(MN);
%dostate(MO);
%dostate(MS);
%dostate(MT);
%dostate(NC);
%dostate(ND);
%dostate(NE);
%dostate(NH);
%dostate(NJ);
%dostate(NM);
%dostate(NV);
%dostate(NY);
%dostate(OK);
%dostate(OH);
%dostate(OR);
%dostate(PA);
%dostate(RI);
%dostate(SC);
%dostate(SD);
%dostate(TN);
%dostate(TX);
%dostate(UT);
%dostate(VA);
%dostate(VT);
%dostate(WA);
%dostate(WI);
%dostate(WV);
%dostate(WY);

Since we were working with 50 states, the SAS macro function was invaluable. I could have written a nested macro or two that would have shortened the process considerably, but I worried that I might miss important information by not doing the job sequentially, so I stuck to single macros for each script. Plus, the single-macro script was easy to write and understand. I named it “dostate” and began with a statement invoking the macro:

%macro dostate(st);

Next, I used SAS to import the Excel file. This can be done by point-and-click with an add-on SAS product called SAS/Access, but since I was running a macro, I needed to include the import procedure:

PROC IMPORT OUT= PAIN.&st._OXY05
DATAFILE= "J:Painkillers&st._oxy05.xls"
DBMS=EXCEL2000 REPLACE;
GETNAMES=YES;
RUN;

Finally, I ended the macro with the obligatory statement:

%mend dostate;

and instructed SAS to run the macro for all 50 states and the District of Columbia:

%dostate(AK);
%dostate(AL);
%dostate(AZ);
%dostate(AR);
%dostate(CA);

…

%dostate(WA);
%dostate(WI);
%dostate(WV);
%dostate(WY);

That made very short work of an import process that otherwise would have taken days, if not weeks. Next, we consolidated each state’s files for the five substances over eight-year period.

libname pain 'j:painkillers';
run;
%macro dostate(st);
data pain.&st._oxy;
set pain.&st._oxy97
pain.&st._oxy98
pain.&st._oxy99
pain.&st._oxy00
pain.&st._oxy01
pain.&st._oxy02
pain.&st._oxy03
pain.&st._oxy04
pain.&st._oxy05;
run;
%mend dostate;
%dostate(AK);
%dostate(AL);
%dostate(AZ);
%dostate(AR);
%dostate(CA);
%dostate(CO);
%dostate(CT);
%dostate(DC);
%dostate(DE);
%dostate(FL);
%dostate(GA);
%dostate(HI);
%dostate(IA);
%dostate(ID);
%dostate(IL);
%dostate(IN);
%dostate(KY);
%dostate(KS);
%dostate(LA);
%dostate(MA);
%dostate(MD);
%dostate(ME);
%dostate(MI);
%dostate(MN);
%dostate(MO);
%dostate(MS);
%dostate(MT);
%dostate(NC);
%dostate(ND);
%dostate(NE);
%dostate(NH);
%dostate(NJ);
%dostate(NM);
%dostate(NV);
%dostate(NY);
%dostate(OK);
%dostate(OH);
%dostate(OR);
%dostate(PA);
%dostate(RI);
%dostate(SC);
%dostate(SD);
%dostate(TN);
%dostate(TX);
%dostate(UT);
%dostate(VA);
%dostate(VT);
%dostate(WA);
%dostate(WI);
%dostate(WV);
%dostate(WY);

Again, it was a deceptively simple script. First, I gave my dataset a name:

data pain.&st._oxy;

Next, I told SAS to combine all state files into one dataset, using the “set” command:

set pain.&st._oxy97
pain.&st._oxy98
pain.&st._oxy99
pain.&st._oxy00
pain.&st._oxy01
pain.&st._oxy02
pain.&st._oxy03
pain.&st._oxy04
pain.&st._oxy05;

I used roughly the same procedure to stack tables together for all states, creating eight U.S. files (one for each year). Then, I used a simple structured query language (SQL) statement in SAS to join the data together for all eight years, and threw in some calculated statements designed to look at year-by-year percentage changes, as well as the amount of change between 1997 and 2005. This gave us our master table (at least, for oxycodone). It was good, but not enough.

We could run a quick query to figure out where the most oxycodone was sold: [Fig.6 – Click for larger image]

So we now knew that the most oxycodone was sold a Missouri area where the ZIP code began with “630.” But what did this really tell us? Not much. It could be that the three-digit ZIP code in Missouri was larger than other three-digit ZIP codes across the country. Or that it had more people.

It appeared to be located southwest of St. Louis, Mo.: [Fig.7 – Click for larger image]

I consulted Census data. Although the American Community Survey is ramping up, it wasn’t adequate. The ACS is an annual report designed to replace the “long form” from the decennial Census and ultimately (one hopes) will provide a wealth of demographic information for areas down to the neighborhood level. Unfortunately, in 2006, it wasn’t reporting much more than rolling, three-year averages for places with more than 65,000 people. That left out a lot of three-digit ZIP codes.

The 2000 Census, while somewhat long in the tooth, offered about the only plausible source of demographic information for the three-digit ZIP code. I began by analyzing data from the Summary File 1 release, which is known as a “100-percent” file, meaning it includes data about every person. Subsequent releases contain more detailed data, such as income and housing figures, but are based on a 1-in-6 household sample and are subject to margins of error.

Using the SF1 release data, I found that it contained an older and generally more diverse population than the national average. But I needed to get some sort of an idea of the use in the area. If there were a huge number of people, then maybe 624,480 grams of oxycodone wasn’t so much, after all. I wrote another SAS script to pull some relevant demographic information for each three-digit ZIP code:

libname census 'j:painkillers';
data census.sf1combo;
merge census.usgeosf1
census.us10001
census.us10002;
by logrecno;
run;
data census.sf1zcta3 (keep=logrecno
sumlev
name
state
county
geocomp
stusab
zcta3
p013001
p013002
p013003
p004001
p004002
p004003
p004004
p004005
p004006
p004007
p004008
p004009
p004010
p004011);
set census.sf1combo;
where sumlev='850' and geocomp='00';
run;
proc delete data=census.sf1combo;
quit;

The script yielded a dataset that looked something like this: [Fig.8 – Click for larger image]

There were some advantages and disadvantages to using the 2000 Census data. The disadvantages were that it was only good for one year, and it would yield a conservative estimate, since the Census figures were collected in 1999, and the DEA data ran from 1997 to 2005. The advantages were that it was the best – and pretty much, the only – data available for the three-digit ZIP codes used by the DEA.

Another major disadvantage couldn’t really be attributed to the Census Bureau or the DEA. Anyone who’s ever worked with ZIP code data becomes painfully aware that a ZIP code isn’t really a geographic entity, at least not in the conventional sense. Typical geographic entities are polygons, with borders. Think of states, counties, cities – almost every Census geographic file is a polygon, with boundaries.

ZIP codes are a little different. The U.S. Postal Service defines ZIP codes as postal delivery routes. They can be lines, they can be points, or they can be polygons. Every decade, the Postal Service and Census Bureau sit down and work out extremely rough approximations of ZIP code boundaries, called “ZIP code tabulation areas,” or ZCTAs. It’s a nice effort, but the ZIP codes in polygons don’t always coincide with the ZIP codes of specific places.

This would be an issue later.

Meanwhile, I had a little more refining to do on my scripts. I returned to my original script that had created a SAS dataset for each substance and each ZIP code. I used another very simple SQL statement to join the master dataset with the Census demographic file. Then, I added a few calculations, showing retail distribution by grams per 100,000 residents, as well as the percentage change in retail distribution between years and between 1997 and 2005. The percentage change field would loom especially large in my later reporting.

Finally, I combined all state and substance data for all years and all ZIPs. I began our analysis in earnest:

In 1997, nearly 50 million grams of the five major painkillers had been sold to Americans. In 2005, that number had nearly doubled, to roughly 94 million grams – enough to give 300 mg of painkiller to every man, woman and child in the nation.

Bingo.

But we still needed to know where the growth had occurred. Unless you spend your entire life analyzing three-digit ZIP codes – and I’m not sure anyone does – trying to make sense of patterns by looking at more than 800 rows of numbers for five separate substances just doesn’t make sense. Even if you can pick out the highs and lows, finding geographic patterns across the entire country just isn’t going to be done.

So I took my SAS tables and exported them into a DBF format, then pulled the data into ArcView v3.2a. Although. ESRI, the Redlands, Calif.-software company that created ArcView has come out with several updates (the most current being ArcGIS v9), I’m most comfortable using the older program, and I think the learning curve for researchers is considerably less steep.

One of the beauties of ArcView is its simplicity. You start out by adding a geographic “theme,” in this case, three-digit ZIP codes. I downloaded the boundary files to create the theme from the Census Web site.

The results appear in the “View1” window on the right: [Fig.9 – Click for larger image]

Next, I added a table, in this case, oxycodone distribution figures for all 800 three-digit ZIP codes.

To add a table, you simply go to the window on the left-hand side, highlight the “Tables” option, click “Add,” and browse to the table you need. Although ArcView theoretically will also take delimited-text files as the basis for tables, I’ve never had any luck with that option. I always convert my tables into DBF files for ArcView:

[Fig.10 – Click for larger image]

I joined my data table with the ArcView three-digit ZIP code theme, simply by opening the theme table for the active, three-digit ZIP theme, like this: [Fig.11 – Click for larger image]

Now, you should have two tables open:

[Fig.12 – Click for larger image]

Simply highlight the common field from the DBF file you want to add and then the common field from the attribute table (think of it as pouring your table’s data into a pitcher of mapping software:

[Fig.13 – Click for larger image]

Finally, click the “Join Tables” icon at the top of the page:

[Fig.14 – Click for larger image]

It may look as though nothing has happened … but your two tables have been joined into one.

Now, you can edit the legend. Click on the “View1” window to make it “active,” then click on the legend editor icon at the top, just below the “Theme” menu:

[Fig.15]

When you get the legend editor dialogue box to appear, select “Graduated Color” as the legend type, then, say, “Y05” as the classification field. That gives me the places with the highest overall distribution for oxycodone.

The legend editor will look like this: [Fig.16]

This generated a color-coded map showing where the changes in oxycodone sales had been most pronounced between 1997 and 2005.

It was fairly clear that oxycodone had moved out of Appalachia. [Fig.17 – Click for larger image]

Next, I created a map showing use per 100,000 people. Several places stuck out, such as suburban St. Louis, where the overall numbers had been highest; Columbus, Ohio; and much of south Florida, especially around Fort Lauderdale:

[Fig.18 – Click for larger image]

Our data helped us establish the cause for the near-doubling of painkiller sales. Almost without exception, the ZIP codes with the highest per capita sales and the greatest increases in sales were older. And where there were older people, we learned, there tended to be more advertising by pharmaceutical companies. We came across a 2005 Government Accountability Office study showing that direct-to-consumer (DTC) advertising had soared, from an $11 billion business in 1997 to roughly $30 billion by 2005.

The third major reason was a little harder to pin down, and there wasn’t really any quantifiable evidence. But we talked to a substantial number of doctors who agreed that sometime in the early 1990s, their thinking on pain had shifted. The old mantra of “no pain, no gain” had given way to a realization that pain often suppressed immune response and seldom served any purpose in people trying to recover, or people who were dying, anyway.

I spoke with Russell Portenoy, the well-respected chairman of New York’s Beth Israel Medical Center. He was heartened by our findings showing increased painkiller sales. Although he’d used words like “medieval” and “barbaric” to describe pain management in the past, Portenoy was low-key and worried about the future. The pendulum, he warned, may begin swinging the other way – less pain medications – if federal prosecutors continued to go after doctors, he said.

By now, we felt we had enough data and information to begin reporting in earnest. We started by selecting “hot spots” for each drug and doing basic archival research to see if any of the heavy and/or increased use had attracted the attention of state and federal investigators. They had, but not in a fashion that we’d expected. We came across several dozen stories where physicians had been arrested for illegally prescribing painkillers:

Va. doctor target of federal OxyContin probe
By MATTHEW BARAKAT
Associated Press Writer
ALEXANDRIA, Va. (AP) – Federal prosecutors in northern Virginia have obtained 18 convictions against dealers and abusers of the prescription pain medicine OxyContin, and are investigating a doctor they consider a co-conspirator in plans to illegally distribute thousands of the pills.
The doctor at the center of the investigation, William E. Hurwitz of McLean, has shut down his practice and defiantly accused the government of interfering with the doctor-patient relationship.
Court documents do not name Hurwitz, but he acknowledges that he is the unnamed McLean physician who served as a source of OxyContin to Timothy Dwayne Urbani, 32, of Manassas. Urbani pleaded guilty earlier this month to dealing between 23,000 and 74,000 OxyContin pills in Tennessee and Virginia.
As part of a plea bargain, Urbani admitted that he participated in an armed robbery of a Fauquier County pharmacy, an arson-insurance scam in Winchester, and other criminal action to obtain OxyContin for resale on the street.
He also said he bought $72,000 worth of OxyContin over 10 months in 2001 and 2002 from a prescription obtained through Hurwitz. The prescriptions allowed Urbani to obtain 1,500 OxyContin pills every two to four weeks.

We felt we had two major issues now: A massive increase in painkillers flooding the United States, and what some activists were calling the DEA’s “War on Physicians.” We reached out to several pain relief organizations, including the Pain Relief Network. The organization had been started by Siobhan Reynolds, a New Mexico woman whose ex-husband, Sean, had been incapacitated by pain from a rare neuromuscular disorder until he found a physician who would prescribe massive doses of OxyContin. Although they were divorced, Siobhan was still driving Sean hundreds of miles to Oklahoma, where a doctor (who had run afoul of Arkansas medical authorities for his heavy volume of prescriptions) provided him with the drugs he needed to function.

Siobhan was articulate and very quotable. She also hated the DEA with a passion, which meant we’d have to work doubly hard to balance the story. But there was no doubt that doctors were being arrested, and those arrests would have to be examined.

From a demographic standpoint, Myrtle Beach was perfect. It had an older population. Plenty of people worked in the service sector, often low-income transients who were more likely to abuse pain medications than other groups. It had a thriving medical community. And it would help us emphasize the point that the increase in pain medication use wasn’t taking place in Appalachia, or New York, or Los Angeles; it was happening in Everytown, U.S.A.

I flew to South Carolina in November for an interview with the husband of one of the imprisoned doctors. He was a retired U.S. Postal Service worker who made a compelling argument for his wife. She had worked for the Myrtle Beach clinic for two months before quitting in disgust. Roughly two years later, DEA agents showed up at her house during breakfast and hauled her off to jail. If she didn’t cooperate with federal prosecutors, she faced a 100-year sentence under a federal drug kingpin statute.

One of her former colleagues at the pain clinic had hung himself in his mother’s back yard after pleading guilty. Benjamin Moore left a suicide note railing against federal prosecutors:

“I know that the prosecution couldn't care less if I rot in jail the rest of my life. As a matter of fact they might prefer it. But they might be willing to negotiate if I can give them what they want. So, since I'm being extorted by the "legal" system, basically blackmailed, I will admit guilt. But what do I admit guilt to? Medicare/Medicaid fraud? If I'm guilty of it, yes. But no one has shown me that I am. Prescribing drugs illegitimately? Only by mistake, not intentionally. But I would concede that if I had to. Theoretically if the penalty were a mandatory death sentence, then I would rather go to trial. I'm not as afraid of death as I am loss of freedom. Death offers freedom, rest, and hopefully peace. I'd rather live free or die.”

Bordeaux’s husband was incredibly angry about the prosecution. When his wife’s attorney returned from a meeting with prosecutors, he recommended she plead guilty as quickly as possible: “The first hog to the trough gets the best slops,” he told them. She fired him and refused to plead guilty. After a short trial, she was sentenced to eight years. She appealed, and the sentenced was reduced to two years.

I returned with one side of the story and began working on the other side. The problem was, no one at the DEA really wanted to talk about physician arrests, or what pain relief advocates were calling a “war on doctors.” I was told by one DEA official in Washington that it had been decided: It would be better to trust the AP to write a balanced story, even absent the DEA’s participation, than to have to rebut questions about the DEA’s prosecution of family physicians.

On my way out of town, we stopped by Grand Strand Regional Medical Center, one of the larger hospitals in the area and spoke with John Clark, a pain management specialist and chief of staff at the hospital. Clark said, yes, untreated pain was a problem, but so was painkiller abuse. Almost as an aside, he mentioned that he wasn’t prescribing pain medications anymore, because the risks of prosecution appeared to be increasing.

We continued to plug away on the story. If the DEA wouldn’t talk, perhaps some of the federal prosecutors who had won convictions would be willing to talk. Surely, their work had taken dangerous doctors who ran “pill mills” off the street. And just as surely, they’d be glad to talk about it.

I spoke with several Justice Department officials around the country, all on background or off-the-record. In general, they were glad to have won high-profile convictions against doctors; so much of their work with illegal drugs centered around sending poor, minority defendants to jail. A couple of conversations seemed straight out of Bonfire of the Vanities: It was much more rewarding to send white, well-educated physicians to jail than to prosecute poor minorities, who were much easier targets and who made up the bulk of the nation’s prison population.

In the end, though, none wanted to appear to be bragging about packing trusted family physicians off to jail for years on end. I was also told that the Justice Department was too busy trying to help former Attorney General Alberto Gonzalez keep his job to address any other run-of-the-mill concerns, such as whether or not the Controlled Substances Act of 1934 was being used appropriately to judge physician performance.

Ultimately, we gave up any hope of trying to get the DEA or the U.S. Justice Department to comment. We ended up poring over hundreds of pages of congressional testimony, thousands of court documents, and dozens of Web pages to piece together the DEA’s approach to prosecuting physicians. We found more than 100 prosecutions of physiciains over a three-year period. The DEA deputy assistant administrator, Joseph Rannazzisi, summed up the situation by telling lawmakers that fewer than 1 percent of the nation’s physicians were illegally supplying prescription drugs to patients. We also found DEA testimony that indicated the No. 1 supplier of illegal prescription drugs tended to be friends and family members.

Our national story was finished in short order. Because of the importance of the story, we decided we would take the extra step and make all of our data available not just to AP bureaus in all 50 states for local stories, but to the more than 6,000 print and broadcast outlets that own the Associated Press.

To head off potential unhappiness from members, we put together an extensive primer on using the data, explaining such nuances as the units sold (grams) and how to obtain very extensive information on three-digit ZIP locations (the Census Bureau Web site). We also cautioned members about relying too much on scare tactics from local law enforcement and health authorities. True, there had been several hundred deaths from people who used OxyContin, but those deaths were almost always caused by a combination of OxyContin and contraindicated drugs.

While our bureaus prepared stories, I put together five maps for every state (plus the District of Columbia and New York metro area) in the country, showing oxycodone, meperidine, hydrocodone, morphine and codeine use by three-digit ZIP code. Doing the maps was labor-intensive and time-consuming, but ultimately made the lives of reporters across the country much easier.

To do the maps, I began with the basic ArcView map I’d created to analyze oxycodone use:

[Fig.19]

Next, I used a query tool to create a map for an individual state:

[Fig.20 – Click for larger image]

For purposes of this example, I’ll use Alabama. I clicked on “State” as the field, then the “=” sign, and then “AL.”

[Fig.21 – Click for larger image]

Then, I clicked on the “Select From Set” button on the right, followed by the “New Set” button above it.
When I did that, the state turned yellow in the map:

[Fig.22 – Click for larger image]

I closed the dialog box, then selected “Convert to Shapefile” from the “Theme” menu at the top:

[Fig.23]

I get a dialog box and save the theme as “AL_oxy.”

I get a dialog box asking me if I want to add the theme to the ArcView project; I click on “OK,” and a new theme appears above the one I’ve been using.

[Fig.24 – Click for larger image]

I wanted to do two things, next: Highlight Alabama, and get rid of the rest of the country.

I needed to do three things: Check the “AL_oxy.shp” theme click anywhere in the general vicinity of the rectangular boundaries of the theme to make it active uncheck the three-digit theme to hide the rest of the country

[Fig.25a]

When I did that, the view looked like this:

[Fig.25]

I used the magnifying glass icon to size and center Alabama:

[Fig.26 – Click for larger image]

Then, as with the national map, I used the Legend Editor to see where the greatest increases occurred:

[Fig.27]

I selected “Graduated Color” and “Chg97_05” in the legend and came up with this:

[Fig.28 – Click for larger image]

Evidently, the greatest percentage increase in oxycodone sales over the eight-year period had occurred in northern Alabama.

Next, I needed to label the three-digit ZIPs:

[Fig.29 – Click for larger image]

I selected “ZCTA3” from the dialog box:

[Fig.30]

Now, I had the labels:

[Fig.31 – Click for larger image]

Next, I needed to export the file. So I went to the top menu again, clicked on “View” and selected “Layout.” Then, I clicked on “OK,” leaving “Landscape” as the default view. Another box appeared in ArcView:

[Fig.32 – Click for larger image]

I changed the label in the layout from “View1” to “Pct Chg Oxycodone, 1997-2005” by clicking in the area around the label and typing in the new label. The text box with the label can be expanded or contracted by clicking on the corners and sizing appropriately.

[Fig.33]

Finally, I went to “File,” “Export” at the top of the menu, named the file (al_oxy) and saved it as a bitmap.

[Fig.34 – Click for larger image]

I followed the same procedure 254 more times. I’m sure there was an automated scripting tool available, but it was a trade-off: The time taken for me to master Avenue, ESRI’s scripting language, almost certainly would have exceeded the week or so it took me to create the maps.

Finally, we exported our SAS files by state into spreadsheets and ran a Visual Basic script to create uniform appearances.

Each spreadsheet had 12 worksheets, including a contents page:

[Fig.35 – Click for larger image]

The hyperlinks led to worksheets with specific numbers for each substance:

[Fig.36 – Click for larger image]

We also created a set of worksheets that graphically showed the change in three-digit ZIPs over the eight-year period:

[Fig.37 – Click for larger image]

Meanwhile, reporters in bureaus were struggling to determine the reasons for several outliers in the data. In one Illinois ZIP code, for example, oxycodone sales had shot up 4,400 percent. Since oxycodone had been introduced in 1995, it was reasonable to expect large percentage increases between 1997 and 2005. Even taking that into consideration, though, the figure was a clear outlier. It took several frustrating days on the phone before a medical school expert in Chicago put his finger on the issue: A large hospice located on the boundary of the three-digit ZIP code (but not within it) was most likely responsible, since the adjacent ZIP code containing the hospice showed little or no growth.

Our multimedia wizards in Washington put together a Web page allowing members and the public to look at painkiller trends within their areas, as well as a national overview.

Our photo department followed with a slide show, and our television unit packaged the Myrtle Beach footage with a compelling story about a Marine veteran who felt that large painkiller dosages had given him his life back. A graphic artist illustrated the sweep of the growth in painkillers.

We put the spreadsheets, maps and a PDF “readme” file into zipped folders and placed them on an external FTP folder. Then, we moved an advisory to members about four days before the story was set to run. The story ran on Aug. 20, 2007.

I’d expected some reaction to the story, but we were all a bit overwhelmed when the story ran. CNN, Fox News, MSNBC and dozens of network affiliates credited the AP, and it ran on the front page of nearly 70 newspapers, including The Miami Herald, Arizona Republic and The Detroit News. Scores of other papers, including ones in India and Australia, gave the story prominent play. Virtually every newspaper member posted the story prominently on their Web page during the day.

I received a rather frantic call from an NBC Nightly News producer who wanted to know if the “AP expert” on pain medication could appear on that night’s broadcast. The story was the subject of an editorial cartoon in the Raleigh, N.C., News & Observer, and was the centerpiece on Yahoo! News for several hours.

One of the most surprising accolades, though, came the day after our stories ran. The DEA held a media briefing to tell Washington-based reporters about the dangers posed by hydrocodone. Far from criticizing our story, one agency official told a Washington-based reporter that “we were so glad we could work hand-in-glove with you on this.”

---

Frank Bass is a reporter with the AP's Washington-based investigations team.