Building a Marketing
Dashboard using
Excel and SAS
Tim Walters
Desired Outcome – Dashboard
Sheet and 6 Results Sheets
Client Environmental
Considerations
Client company has software to facilitate transfer of
images between mobile phones and the Internet
Environmental Factor
Resulting Decision
Needed results in workbook for email
distribution among key executives
Workbook must include all data. No
external references allowed
Needed flexibility in certain reporting
parameters
User selects parameters
Adding capable handsets and sites over
time
Stop system when new information
encountered
Unknown technical skills of people running
system
Make interface simple and from familiar
source (Excel)
Expected large influx of customers
Use SAS for processing
May not have SAS PC Files module
Write output files using ODS
System Overview
User Opens
Excel Workbook
User Completes
Workbook Form
User Runs –
Launches SAS &
Excel Monitors
SAS Processing
Results
Workbook
Populated
User Distributes
1. User Opens Excel Workbook
Folder
Location of
3. User Runs – Launches SAS
Private Sub Run_Click() access_type = &H400 still_active = &H103
'this will be used to delete files and check for existence Set fsofile = CreateObject("scripting.filesystemobject") a = ThisWorkbook.path
On Error Resume Next
fsofile.deletefile (a & "\phone_errors.xls") 'Deletes existing file fsofile.deletefile (a & "\site_errors.xls") 'Deletes existing file
sas_location = Workbooks("company Reports.xls").Worksheets("Sheet1").Range("B1").Value sas_program = a & "\ReadWeeklyFiles.sas"
datec = "'" & Application.WorksheetFunction.Text(Calendar1, "ddmmmyy") & "'" & "d"
runparm = a & "\$" & datec & "$" & WeeklyFile & "$" & Weeks & "$" & CountryCutoff & "$" & HeavyUsers & _ "$" & PreviouslyActive & "$" & AccountsUsingBackup & "$" & UploadActivity & "$" & SitesConfigured _ & "$" & UploadSites & "$" & NumDaysUploads & "$" & NumDaysActiveUploads & "$" & _
OTA_Downloads
completeline = sas_location & " -sysin " & sas_program & " -log " & a & " -noprint -sysparm " & runparm 'uses shell execute
taskid = Shell(completeline, 1)
3. User Runs – Excel Monitors
Do 'loop continuously 'check on the process
GetExitCodeProcess hproc, lexitcode 'allow event processing
DoEvents
Loop While lexitcode = still_active currpath = ActiveWorkbook.path
If fsofile.fileexists(currpath & "\phone_errors.xls") Then
MsgBox ("New phones exists. Please update handsets.csv and re-submit") Workbooks.Open (currpath & "\phone_errors.xls")
Workbooks.Open (currpath & "\handsets.csv") stopper = "Yes"
End If
If fsofile.fileexists(currpath & "\site_errors.xls") Then
MsgBox ("New sites exists. Please update parameters.csv and re-submit") Workbooks.Open (currpath & "\site_errors.xls")
Workbooks.Open (currpath & "\parameters.csv") stopper = "Yes"
End If
If stopper <> "Yes" Then Workbooks.Open (currpath & "\Weekly Dashboard.xls") Me.Hide
Unload Me End Sub
4. SAS Processing – ReadWeeklyFiles.sas
Read 14 Parameters Passed
%macro createmacvars;
%global week_date folder filedate ;
%let folder=%scan(%quote(&sysparm),1,$);
%let week_date=%scan(%quote(&sysparm),2,$);
%let filedate=%scan(%quote(&sysparm),3,$);
%let weeks=%scan(%quote(&sysparm),4,$);
%let country_cutoff=%scan(%quote(&sysparm),5,$);
%let heavy_users=%scan(%quote(&sysparm),6,$);
%let previous_active=%scan(%quote(&sysparm),7,$);
%let accounts_using_backup=%scan(%quote(&sysparm),8,$);
%let upload_activity=%scan(%quote(&sysparm),9,$);
%let sites_configured=%scan(%quote(&sysparm),10,$);
%let upload_sites=%scan(%quote(&sysparm),11,$);
%let num_days_uploads=%scan(%quote(&sysparm),12,$);
%let num_days_active_uploads=%scan(%quote(&sysparm),13,$);
%let ota_downloads=%scan(%quote(&sysparm),14,$);
%mend;
%createmacvars;
4. SAS Processing – ReadWeeklyFiles.sas
Program Aspects
All files must be in the same folder. Folder
name used for input/output files
Input
%let userfile=&folder&filedate Users & Accounts.csv;
%let parafile=&folder.Parameters.csv;
%let handfile=&folder.Handsets.csv;
Output
%let out_errors=&&folder.phone_errors.xls;
%let out_weekly=&&folder.weekly.xls;
4. SAS Processing – ReadWeeklyFiles.sas
Stop Program for New Data
%macro stopp;
%if &obs_errors > 0 %then %do;
ods html file="&&out_errors" style = printer headtext="<STYLE> TD {MSO-NUMBER-FORMAT:\#\#\,\#\#\#\,\#\#0;}</STYLE>"; title; proc print data=phone_errors(keep=phone_text_1_20) &printstyle; run; ods html close; %abort; %end;
%if &site_o_errors > 0 %then %do;
ods html file="&&out_site_errors" style = printer headtext="<STYLE> TD {MSO-NUMBER-FORMAT:\#\#\,\#\#\#\,\#\#0;}</STYLE>"; title; proc print data=site_errors(keep=site_code) &printstyle; run; ods html close; %abort; %end; %mend;
/* data file of new types not in table */ data phone_errors; set company.userphone; if phone_type=" "; run; %obsnvars(phone_errors); data site_errors; set company.usersite; if site_type=" "; run; %obsnvars(site_errors);
/* invoke stopp macro -- if phone or site errors, stops awaiting user correction */
%stopp; /* this macros gets the number of
observations and number of columns for certain datasets */
%macro obsnvars(ds);
%global dset vars_phones vars_sites obs_errors sites_obs site_o_errors;
%let dset=&ds; %let dsid =
%sysfunc(open(&dset)); %if &dsid %then
%do;
%if &ds=phone_errors %then %let obs_errors=%sysfunc(attrn(&dsid,N obs)); %if &ds=site_errors %then %let site_o_errors=%sysfunc(attrn(&dsi d,Nobs)); %let rc = %sysfunc(close(&dsid)); %end; %else
%put Open for data set &dset failed - %sysfunc(sysmsg());
4. SAS Processing – ReadWeeklyFiles.sas
Output Files Using ODS
Main File
ods html file="&&out_weekly"
style = printer
headtext="<STYLE> TD
{MSO-NUMBER-FORMAT:\#\#\,\#\#\#\,\#\#0;}</
STYLE>";
title;
proc print
data=weekly_report_out(wher
e=(week_activated ne -10))
&printstyle;
var _numeric_ ;
run;
ods html close;
Parameters File Fed Back to Excel
data uploads_28_final;
weeks=symget('weeks');
heavy_users=symget('heavy_users');
previous_active=symget('previous_active');
country_cutoff=symget('country_cutoff');
run;
ods html file="&&out_within_28" style = printer
headtext="<STYLE> TD
{MSO-NUMBER-FORMAT:\#\#\,\#\#\#\,\#\#0;}</STYLE>";
title;
proc print data=uploads_28_final &printstyle;
run;
5. Results Workbook Populated –
Weekly Dashboard.xls
VBA Code in Microsoft Excel Objects This
5. Results Workbook Populated –
Weekly Dashboard.xls
Sub Chart_Update() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False a = ThisWorkbook.Path pp = "Weekly Dashboard.xls" With Workbooks(pp)' Clear existing worksheets
.Worksheets("within28").Cells.ClearContents .Worksheets("weekly").Cells.ClearContents
.Worksheets("countries active").Cells.ClearContents End With
' start by processing within 28 file Workbooks.Open (a & "\within28.xls")
Workbooks("within28.xls").Worksheets("within28").Cells.Select ‘selects all cells from workbook Selection.Copy ‘copy
