Friday's Innovation Inspiration: Cleaning data in her spare time

0

In my Friday series about innovation, I've decided to include some personal stories of unusual and unique uses of SAS. Once a month or so, I'll swap out the Post-it Notes for a profile of someone who has used SAS in an innovative way to solve a problem or, like Veronica Walgamotte, simply decided that SAS is faster than pencil and paper. She used SAS rather than manually sorting through data to match, merge, sort and analyze before printing. Check out her story.

WAYNETTE TUBBS: Hi Veronica. I want to hear about your unique use of SAS, but before we get to that, can you tell me a little about what you do at SAS?
VERONICA WALGAMOTTE: Sure. I’m a software manager at SAS in the Knowledge Management Center. I work on the Sales Compensation Application. I’ve been doing that work for about 10 years now, part time.

Tell me about how you used SAS in a way that others might not have.
Since I only work part time, my other part time job is volunteering for the Ashburton Elementary School’s PTA (Parent Teacher Association). In the fall of 2010, they asked me to take on the PTA membership. We had 420 members. The school has about 772 students; that is actually a large elementary school for the area. We had a population explosion in 2010 and increased our enrollment by 150 kids. Every year, the PTA publishes a directory for the PTA parents. It includes student’s names, addresses and phone numbers, and the email addresses and cell phone numbers of the student’s parents when available.

My colleague on the directory said she would type everything in if I would pull all of the information together. I told her I would do that because I knew that I could pull the data together quickly and easily with SAS. I used SAS v9.2 to read in the data from the Excel spreadsheets. We worked with data from:

  • A spreadsheet containing all of the PTA membership information.
  • One containing all of the student information from the school.
  • A spreadsheet of withholds: This is a group of students whose parents do not want information published or want only certain information published.
  • I also built a spreadsheet mapping given names to nicknames. For instance, Margaret mapped to Maggie.

Tell me about the end result.
I used ODS (Output Delivery System) to print HTML files. I output the data in two ways because the printed directory provides the student information in two ways:

  1. Section one alphabetically lists each student by last name and includes the student’s phone number and teacher.
  2. Section two is alphabetized by each teacher’s name. In this section, we list the students for each teacher and include the contact information we are allowed to print and have available.

The program has logic that says, if we have this and if it comes from here and they’re not a withhold, print it here (lots of if/then else statements). Getting all of that right was kind of tedious, but it was certainly much easier to do it with SAS than to do it by hand. The final back breaker was that the directory had to be delivered to the printer in Microsoft Publish. No worries, though. Basically, I just cut and pasted from the HTML files into Microsoft Publish.

Once the printer publishes this directory, how is it used?
All of the PTA members get a free directory, so we print 450 directories. The other 30 are held in reserve for anyone who joins after the school year begins. The directory is the contact information for all of the students at the school. Parents use the directory for birthday parties, play dates and socials. Parents can look up the e-mail address or phone number of another parent - it’s like a phonebook for the school. 

As far as I know, this is the first time it’s been done using SAS.

I’ll bet! Were you able to take steps along the way to make this easier for next year?
Oh yes! We’ve compiled notes to make sure that next year, when we have to do this again, we have a template to go through. And, of course, I kept all of the SAS code.

Simple, yet smart. Make sure you don't miss a single post by having them delivered to your inbox. You'll find directions at the bottom of this page. Have a happy Friday!

Share

About Author

Waynette Tubbs

Editor, Marketing Editorial

+Waynette Tubbs is the Editor of the Risk Management Knowledge Exchange at SAS, Managing Editor of sascom Magazine and Editor of the SAS Tech Report. Tubbs has developed a comprehensive portfolio of strategic business and marketing communications during her career spanning 15 years of magazine, marketing and agency work.

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top