## Calculate Net Promoter Score in PROC TABULATE or SQL

This demonstrates an easy way to calculate the Net Promoter Score, a measure of customer loyalty, in SAS’s PROC TABULATE or PROC SQL. I prefer to use PROC TABULATE because of its power for various summaries.

In the example we repeated the Net Promoter Score survey in two different months, and in one pass we calculate the separate Net Promoter Scores for each month.

Normally the proportions of Promoters and Detractors are separately calculated and then subtracted, but it can be shown with a little algebra that only one ratio needs to be calculated. This is the trick we use to simplify the work in SAS.

/* create an example data set with responses */
data nps;
input date nps;
format date monyy7.;
informat date yymmdd10.;
datalines;
2012-01-01 10
2012-01-01 9
2012-01-01 9
2012-01-01 7
2012-01-01 6
2012-04-01 10
2012-04-01 9
2012-04-01 7
2012-04-01 1
;

/* transform */
data nps;
set nps;
if nps in (9 10) then nps_promoter = 1; /* 9 and 10 are promoters */
else if nps in (0 1 2 3 4 5 6) then nps_promoter = -1; /* 0-6 are detractors */
else nps_promoter = 0; /* 7 and 8 are passive */
run;

/* report */
proc tabulate data=nps;
class date;
var nps_promoter;
table
date,
n='Number of survey responses'*f=comma10.
nps_promoter='Net promoter score' *mean=''*f=percent10.0
;
quit;

This gives a Net Promoter Score of 40% for January 2012. To verify this is correct, let’s calculate it using the traditional method. There are three Promoters out of five responses (60%) and one detractor (20%), so 60% – 20% = 40%. So the calculation in PROC TABULATE is correct.

If you need the results in a data set, here is how to calculate Net Promoter Score using PROC SQL. This method does not require the transformation performed in the DATA step.

proc sql;
create table nps_monthly as
select
date,
mean(case
when nps in (9 10) then 1
when nps in (7 8) then 0
when nps in (0 1 2 3 4 5 6) then -1
end) as net_promoter_score format percent10.0
from
nps
group by
date
order by
date;
quit;