1 / 18

Building an Excel to SAS to Excel System

Building an Excel to SAS to Excel System. Tim Walters InfoTech Marketing. 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. System Overview.

edward-powell
Download Presentation

Building an Excel to SAS to Excel System

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

  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.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) hproc = OpenProcess(access_type, False, taskid)

  8. 3. User Runs – Excel Monitors Do 'loop continuously 'check on the process GetExitCodeProcesshproc, 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

  9. 4. SAS Processing – ReadWeeklyFiles.sasRead 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;

  10. 4. SAS Processing – ReadWeeklyFiles.sasProgram 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;

  11. 4. SAS Processing – ReadWeeklyFiles.sasStop Program for New Data /* 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; %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;

  12. 4. SAS Processing – ReadWeeklyFiles.sasOutput Files Using ODS 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; ods html close; Main File ods html file="&&out_weekly" style = printer headtext="<STYLE> TD {MSO-NUMBER-FORMAT:\#\#\,\#\#\#\,\#\#0;}</STYLE>"; title; proc print data=weekly_report_out(where=(week_activated 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.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 Workbooks(pp).Worksheets("within28").Activate ‘activate worksheet you want Range("A1").Select ActiveSheet.Paste

  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

  18. For More Information Tim Walters InfoTech Marketing 720-732-4588 tim@infotechmarketing.net

More Related