A Blog on Analytics and Marketing

SAS, Marketing, Predictive Modeling, Statistics

Archive for January, 2009

SAS: Merge a very large dataset and a small dataset efficiently

Posted by phillippeng on January 24, 2009

I need to append zip penetration data to a campaign file. This is a very typical data appending job, in which the campaign file is a very large file with over 24mm records while the zip penetration file has only 13,000 records. PROC SQL can make it, but it takes more than one hour to finish the process (running on a fast server). Using the hash table is a more efficient way to achieve the goal.

Macro Code:
%macro appnd_zip(dsin, dsout);
data home.&dsout.;
if 0 then set home.penetration_zip5(keep=zip5 zip_penetration);
declare hash zip(dataset: “home.penetration_zip5”);

do until(eof);
set home.&dsin.(rename=(ms403=zip5)) end=eof;
if zip.find(key:zip5) = 0 then output;
else do;
call missing(zip_penetration);


In this macro, I first define a hash table based on the small dataset (penetration at zip5 level). Then I match it against the large dataset based on the matchkey zip5. When there’s a match, append the data. When there’s no any match found, assign a missing value to it. This is equivalent to a left join. If you want to do an inner join, you need to output only when there’s a match found. The processing time is reduced to about 15 minutes.


Posted in SAS, Uncategorized | Leave a Comment »

Download them all!

Posted by phillippeng on January 14, 2009

We have a vertical list dry testing project. Content team posts all the vertical lists from different vendors on an internal portal. There are 94 vertical lists need to be downloaded. Oh! My Ghosh! Anybody who’s taking a lead on this would have a headache if he/she does it manually.

How about downloading them all at a time? Can we do that? Oh, yeah.

Here’s my personal recommendation: Use download software “flashget”. Here’s the download page of the English version:


After you download and install it, you should reboot your computer before it really works for you fully. I thought I need the administrator authorization to install it. Out of my anticipation, it passes through that and get installed without bothering the IT buddy. Woh! It’s green.

After log into the download page of my vertical lists, I can right click and choose “Download all with Flashget”. No more headache with downloading many files.

Posted in Computer | Leave a Comment »

[Book Reading] Marketing Metrics: 50+ metrics every executive should master

Posted by phillippeng on January 10, 2009

“Marketing Metrics: 50+ metrics every executive should master” is published by University of Pennsylvania Wharton School Publishing in 2006. The authors are Paul W. Farris, Neil T. Bendle, Phillip E Pfeifer and David J. Reibstein.

As the title suggested, the book discusses over 50 marketing metrics. These metrics are divided into 9 categories based on what they are measuring:

1. Share of hearts, minds and markets. (competitive analysis)
2. Margins and profits. (Profitability analysis)
3. Product and portfolio management
4. Customer profitability
5. Sales force and channel management
6. Pricing strategy
7. Promotion
8. Advertising media and web metrics
9. Marketing and Finance

At the age of customer centric, I will focus on sharing their more detailed discussions on customer profitability later.

Posted in Marketing | Leave a Comment »