Building an Excel to SAS to Excel System

1
Building an Excel to SAS to Excel System

• Tim Walters
• InfoTech Marketing

2
Desired Outcome Dashboard Sheet and 6 Results
Sheets
3
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
4
System Overview
5
1. User Opens Excel Workbook
Folder Location of SAS
6
2. User Completes Workbook Form
7
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.filesys
  temobject")
• 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").Val
  ue
• sas_program a "\ReadWeeklyFiles.sas"
• datec "'" Application.WorksheetFunction.Te
  xt(Calendar1, "ddmmmyy") "'" "d"
• runparm a "\" datec "" WeeklyFile
  "" Weeks "" CountryCutoff ""
  HeavyUsers _
• "" PreviouslyActive ""
  AccountsUsingBackup "" UploadActivity
  "" SitesConfigured _
• "" UploadSites ""
  NumDaysUploads "" NumDaysActiveUploads ""
  _ OTA_Downloads

8
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")

9
4. SAS Processing ReadWeeklyFiles.sasRead 14
Parameters Passed

• macro createmacvars
• global week_date folder filedate
• let folderscan(quote(sysparm),1,)
• let week_datescan(quote(sysparm),2,)
• let filedatescan(quote(sysparm),3,)
• let weeksscan(quote(sysparm),4,)
• let country_cutoffscan(quote(sysparm),5,)
• let heavy_usersscan(quote(sysparm),6,)
• let previous_activescan(quote(sysparm),7,)
• let accounts_using_backupscan(quote(sysparm),
  8,)
• let upload_activityscan(quote(sysparm),9,)
• let sites_configuredscan(quote(sysparm),10,)
  
• let upload_sitesscan(quote(sysparm),11,)
• let num_days_uploadsscan(quote(sysparm),12,)
  
• let num_days_active_uploadsscan(quote(sysparm
  ),13,)
• let ota_downloadsscan(quote(sysparm),14,)
• mend
• createmacvars

10
4. SAS Processing ReadWeeklyFiles.sasProgram
Aspects

• All files must be in the same folder. Folder
  name used for input/output files
• Input
• let userfilefolderfiledate Users
  Accounts.csv
• let parafilefolder.Parameters.csv
• let handfilefolder.Handsets.csv
• Output
• let out_errorsfolder.phone_errors.xls
• let out_weeklyfolder.weekly.xls

11
4. SAS Processing ReadWeeklyFiles.sasStop
Program for New Data

• macro stopp
• if obs_errors gt 0 then do
• ods html file"out_errors"
  style printer headtext"ltSTYLEgt TD
• MSO-NUMBER-FORMAT\\\,\\\\,\
  \0lt/STYLEgt"
• title
• proc print dataphone_errors(keep
  phone_text_1_20) printstyle
• run
• ods html close
• abort
• end
• if site_o_errors gt 0 then do
• ods html file"out_site_errors"
  style printer headtext"ltSTYLEgt TD
• MSO-NUMBER-FORMAT\\\,\\\\,\
  \0lt/STYLEgt"
• title
• proc print datasite_errors(keeps
  ite_code) printstyle
• run
• ods html close
• abort
• end

/ data file of new types not in table / data
phone_errors set company.userphone
if phone_type" " run data site_errors
set company.usersite if site_type"
" run / invoke stopp macro -- if phone or
site errors, stops awaiting user correction
/ stopp
12
4. SAS Processing ReadWeeklyFiles.sasOutput
Files Using ODS
Parameters File Fed Back to Excel data
uploads_28_final weekssymget('weeks')
heavy_userssymget('heavy_users')
previous_activesymget('previous_active')
country_cutoffsymget('country_cutoff') run ods
html file"out_within_28" style printer
headtext"ltSTYLEgt TD MSO-NUMBER-FORMAT\
\\,\\\\,\\0lt/STYLEgt" title proc print
datauploads_28_final printstyle run ods html
close

• Main File
• ods html file"out_weekly" style printer
  headtext"ltSTYLEgt TD
• MSO-NUMBER-FORMAT\\\,\\\\,\\0lt/
  STYLEgt"
• title
• proc print dataweekly_report_out(where(week_acti
  vated ne -10)) printstyle
• var _numeric_
• run
• ods html close

13
5. Results Workbook Populated Weekly
Dashboard.xls

• VBA Code in Microsoft Excel Objects ? This
  Workbook

14
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.ClearContent
  s
• .Worksheets("weekly").Cells.ClearContents
• .Worksheets("countries active").Cells.Clea
  rContents
• 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

15
5. Results Workbook Populated Weekly
Dashboard.xls

• More Chart_Update Macro Statements
• Application.ScreenUpdating True
• Application.DisplayAlerts True
• Application.EnableEvents True
• End Sub

16
6. User Distributes Checks Data, Manually
Saves, and Manually Emails
17
Advantages/Disadvantages of Excel-SAS-Excel System
Advantages Disadvantages
Use Excel charts/formatting Utilize powerful SAS processing User can run from familiar interface Ubiquity of Excel Limit SAS licensing Must know some VBA and its peculiarities May hit Excel row/column limits 64,000 in Excel 2003, 1 million in Excel 2007
18
For More Information

• Tim Walters
• InfoTech Marketing
• 720-732-4588
• tim_at_infotechmarketing.net