SAS Code to Export and Create Pivot Tables

SAS Code to Export and Create Pivot Tables in Excel 2007 By Robert Williams Senior Supply Chain Analyst Strategic Sourcing and Contracts The New York Times Company – Shared Service Center

Overview �Why need pivot tables in Excel 2007 �Excel Step: Excel macro (VB) for pivot table �SAS Steps: SAS code to do these steps ◦ ◦ ◦ Step 1: Export data into Excel Step 2: Open Excel file with the VB macro Step 3: Open Excel file with the exported data Step 4: Invoke VB macro to create pivot table Step 5: Save the Excel file with newly created pivot table �Q&A

Why Pivot Tables in Excel 2007 �Drill down capabilities �User friendly hands-on reports �Management and decision makers are asking for it. �With SAS, the pivot tables can be repeated for production reports on a daily, weekly or monthly basis. �Also, the pivot tables can be performed on multiple subsets of data i. e. by business units, departments and etc.

Excel Step: VB macro to pivot �First, we need to create a Excel macro that does a pivot table on the data. �Easiest way is to use the Excel’s macro recorder (under Developer menu). �Use macro recorder on a sample data to establish the pivot table structure. �Name the macro i. e. “Create_pivottable” �Expand the size data range inside the VB code. �Save the Excel file with the VB macro as a blank file (must be. xlsm)

Excel Step: VB macro to pivot �What does VB code look like? Note the range name and size…

SAS Step 1: Export the data �Export the actual data into an Excel 2007 file. �The data must have the same column names and sheet name for the VB macro.

SAS Step 2: Open Excel file with the VB macro �First, we need to open the Excel file that has the VB macro we created (the blank file). �It is opened using SAS ‘x’ command with the path and file name of the. xlsm file.

SAS Step 3: Open Excel file with the exported data �It is important to open this file after opening the VB macro file. That way, the Excel file with the actual data becomes the active window. �Before doing this, we have to set up the DDE in SAS. DDE stands for Direct Data Exchange. It allows SAS to control Excel. �Once the DDE is set up, we can invoke the VB macro on the exported data.

SAS Step 3: Open Excel file with the exported data �SAS code for DDE set up and opening the Excel file with the exported data.

SAS Step 4: Invoke the VB macro to create pivot table �To invoke the VB or run other Excel commands, I use the SAS “put” statement in the data _null_ i. e. ◦ put "[run(""'Main Wireless Macro for SAS. xlsm'!Create_wireless_pivottable"")]"; � The data _null_ is linked to the DDE under the “file” statement. �Note: I put the “sleep” statement to allow Excel time to fully open the Excel files before the next command.

SAS Step 5: Save the Excel file with the newly created pivot table �At this point, we just created pivot table of the exported data. �We save it while still in the data _null_ step using the DDE with this SAS line ◦ put '[file. close(true)]'; � It closes the active Excel file while setting the Excel saved option as ‘true’.

SAS Step 5: Save the Excel file with the newly created pivot table �At this point, we just created pivot table of the exported data. �We save it while still in the data _null_ step using the DDE with this SAS line ◦ put '[file. close(true)]'; � It closes the active Excel file while setting the Excel saved option as ‘true’. �Excel file with the VB macro is still open. �Close Excel using put "[quit()]"; Don’t forget the sleep before doing this.

Q&A �Questions? �Contact: Robert Williams The New York Times Company Shared Service Center 101 West Main Street, Suite 2000 Norfolk, VA 23510 robert. williams@nytssc. com

References Linking SAS and Microsoft Products by Nat Wooding, Dominion Virginia Power. Presented at Virginia SAS User’s Group on June 17, 2009. � Step-by-Step in Using SAS DDE to Create an Excel Graph Based on N Observations from a SAS Data Set by Choon. Chern Lim, Mayo Clinic, Rochester, MN. Presented at SUGI 31 � SAS Online Doc Version 9. 2. Cary, NC: SAS Institute Inc. � Excel 2007 Online Help Microsoft Corp. �

SAS Code /* This is to export the wireless data and invoke */ /* the VB macro to create the pivot table on the */ /* exported data. */ %let xlsdir = H: SAS PapersVASUG Spring 2010 Presentation; libname VASUG "&xlsdir"; /* Export the data into Excel prior to doing the pivot table */ PROC EXPORT DATA=VASUG. sample_wireless OUTFILE= "&xlsdir. VASUG Wireless. xlsx" DBMS=EXCEL LABEL REPLACE; SHEET="Data"; run; /* Opens the Excel macro and establish the DDE to run the Excel VB macro */ %let xls. Macro = &xlsdirMain Wireless Macro for SAS. xlsm; options noxsync noxwait; x "'&xls. Macro. '"; /* Opens the Excel macro file first */ data _null_; rc = sleep(2); run; filename sas 2 xl dde 'excel|system'; /* This data step opens the file with the data and runs */ /* the Excel macro to create the pivot table of the data */ data _null_; file sas 2 xl; put "[open(""&xlsdir. VASUG Wireless. xlsx"", 0 , false)]"; rc = sleep(2); put "[run(""'Main Wireless Macro for SAS. xlsm'!Create_wireless_pivottable"")]"; put '[file. close(true)]'; run; /* Closes the Excel application */ data _null_; rc = sleep(5); run; data _null_; file sas 2 xl; put "[quit()]"; run;

Excel VB Code Sub Create_wireless_pivottable() ' Creates a pivot table of all the phone data Sheets. Add After: =Sheets(Sheets. Count) Active. Sheet. Name = "Wireless. Pivot" Active. Workbook. Pivot. Caches. Create(Source. Type: =xl. Database, Source. Data: = _ "Data!R 1 C 1: R 50000 C 30", Version: =xl. Pivot. Table. Version 12). Create. Pivot. Table _ Table. Destination: ="Wireless. Pivot!R 3 C 1", Table. Name: ="Wireless. Pivot. Table", _ Default. Version: =xl. Pivot. Table. Version 12 Sheets("Wireless. Pivot"). Select Cells(3, 1). Select With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("BU"). Orientation = xl. Row. Field. Position = 1 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Vendor Name"). Orientation = xl. Row. Field. Position = 2 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("DEPT"). Orientation = xl. Row. Field. Position = 3 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Wireless Number"). Orientation = xl. Row. Field. Position = 4 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("User Name"). Orientation = xl. Row. Field. Position = 5 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Equipment Make"). Orientation = xl. Row. Field. Position = 6 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Equipment Model"). Orientation = xl. Row. Field. Position = 7 End With

Excel VB Code (cont. ) With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Type of Service"). Orientation = xl. Row. Field. Position = 8 End With Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Current Charges"), _ "Total Current Charge", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Monthly Voice & Data Charge"), _ "Monthly Voice/Data Access Charge", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Additional Airtime Charge"), _ "Additional Airtime Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Data Useage Charge (Excluding Roaming)") _ , "Data Usage Charges (Excluding Roaming)", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Additional Feature Charge"), _ "Additional Feature Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Equipment Charge"), _ "Equipment Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Long Distance Charge"), _ "LD Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Directory Assistance Charge (411 calls)" _ ), "411 Assistance Charge", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Roaming Charge"), _ "Roaming Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Service Level Other Charge"), _ "Other Service Level Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Taxes Surcharges and Regulatory Fees"), _ "Taxes Surcharges and Regulatory Fees", xl. Sum

Excel VB Code (cont. ) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Miscellaneous Usage Charge"), _ "Miscellaneous Usage Charge", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Non-Communications Charge"), _ "Non-Communications Charge", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Messaging Charge"), _ "Messaging Charges", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total Number of Events"), _ "Total Count of Messages", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total voice Minutes of Use (MOU)"), _ "Voice Minutes of Use (MOU)", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Add. Data. Field Active. Sheet. Pivot. Tables( _ "Wireless. Pivot. Table"). Pivot. Fields("Total KB Data Usage"), _ "Data Usage (Kilobytes)", xl. Sum Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Table. Style 2 = "Pivot. Style. Medium 9" Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Row. Axis. Layout xl. Tabular. Row ' Expand to show all details Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("BU"). _ Show. Detail = True ' Removes the subtotals for wireless numbers, User Name, Equipment Make and type of service Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields( _ "Wireless Number"). Subtotals = Array(False, False, _ False, False, False) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("User Name"). _ Subtotals = Array(False, False, False, _ False, False) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Equipment Make"). _ Subtotals = Array(False, False, False, _ False, False) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Type of Service"). _ Subtotals = Array(False, False, False, _ False, False)

Excel VB Code (cont. ) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Equipment Model"). _ Subtotals = Array(False, False, False, _ False, False) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Dept"). _ Subtotals = Array(False, False, False, _ False, False) Active. Sheet. Pivot. Tables("Wireless. Pivot. Table"). Pivot. Fields("Vendor Name"). _ Subtotals = Array(False, False, False, _ False, False) ' Formats the dollars amounts Columns("I: V"). Selection. Number. Format = "$#, ##0. 00" ' Formats the number with thousands separator Columns("X: Y"). Selection. Number. Format = "#, ##0" ' Adjust the column widths Columns("A: A"). Selection. Column. Width = 12 Columns("B: B"). Selection. Column. Width = 18 Columns("C: C"). Selection. Column. Width = 10 Columns("D: D"). Selection. Column. Width = 15 Columns("E: E"). Selection. Column. Width = 25 Columns("F: F"). Selection. Column. Width = 14 Columns("G: G"). Selection. Column. Width = 14 Columns("H: H"). Selection. Column. Width = 14 Columns("I: AA"). Selection. Column. Width = 15

Excel VB Code (cont. ) ' Formats the header to wrap text Rows("4: 4"). Select With Selection. Horizontal. Alignment = xl. General. Vertical. Alignment = xl. Bottom. Wrap. Text = True. Orientation = 0. Add. Indent = False. Indent. Level = 0. Shrink. To. Fit = False. Reading. Order = xl. Context. Merge. Cells = False End With Range("A 1"). Select Active. Window. Zoom = 85 With Active. Sheet. Page. Setup. Print. Title. Rows = "$4: $4". Print. Title. Columns = "" End With Active. Sheet. Page. Setup. Print. Area = "" With Active. Sheet. Page. Setup. Center. Header = "&A". Left. Margin = Application. Inches. To. Points(0. 2). Right. Margin = Application. Inches. To. Points(0. 2). Orientation = xl. Landscape. Paper. Size = xl. Paper. Letter. Zoom = False. Fit. To. Pages. Wide = 1. Fit. To. Pages. Tall = 999 End With End Sub