Lesson 01


Working with SAS® and Microsoft Excel
Lesson 01, Section 1 Demo: Integrating SAS and Microsoft Excel

This demonstration illustrates how to use SAS to import data from Microsoft Excel, process the data in SAS, and then export SAS results to create an Excel report.

Setup

Open the xls01d01.sas program in the demos folder.

Step 1: Open, review, and run the libname.sas program.

  1. In the course folder, open the libname.sas program and examine the statements.

    Open code or syntax in a separate window.

    %let path=S:/workshop/EPGXLM7;
    %let outpath=S:/workshop/EPGXLM7/output;
    libname pg "&path./data";


    1. The path macro variable references the main course folder. This will be used to specify the location of the course folder.
    2. The outpath macro variable references the output folder for the course. This macro variable will be used to specify the output location of the Excel workbooks that are created. 
    3. The LIBNAME statement creates a library reference named pg to the data folder of this course. You will use the pg library to reference all SAS tables throughout the course.
      Note: Depending on where you are taking this course, this code might differ slightly.

  2. Run the libname.sas program and check the log to confirm that the program ran successfully.

  3. In the Libraries pane, confirm that the pg library was created and contains five SAS tables.

    Note: You will be required to execute the libname.sas program every time that you open SAS to set up the library reference and macro variables.

Step 2: Download and open the Microsoft Excel workbook.

  1. Download and open the prdsales_countries.xlsx Microsoft Excel workbook from the data folder. View the Excel workbook and confirm that it contains three worksheets named USA, Canada, and Mexico. Each worksheet contains detailed data about furniture sales for each country.

  2. Close the prdsales_countries.xlsx Excel workbook.

Step 3: Import the data from Excel and prepare a SAS table.

  1. Quickly review the code in Step 3 of the program.

    Open code or syntax in a separate window.

    options validvarname=v7;
    libname pgxl xlsx "&path/data/prdsales_countries.xlsx" access=readonly;
    
    * Create a single SAS data set with additional data preparation *;
    data work.prd_sales_final;
    	length Country $15. State $30.;
    	set pgxl.usa
    		pgxl.canada
    		pgxl.mexico;
    		
    	* Create calculated columns *;
    	TotalDiffPredict = Actual - Predict;
    	PctDiffPredict = TotalDiffPredict / Predict;
    	if TotalDiffPredict < 0 
                   then MonthlyForecast = 'Under Predicted';
    	else if TotalDiffPredict >= 0 
                   then MonthlyForecast ='Over Predicted';
    			
    	* Drop columns *;
    	drop County MonYR;
    	
    	* Format columns *;
    	format Manager_ID z10. 
    		   Actual Predict TotalDiffPredict dollar16.2
    		   PctDiffPredict percent7.2
    		   _CHARACTER_;
    	
    	* Add column labels *;
    	label Country = "Business Country"
    		  Actual = "Actual Income"
    		  MonthlyForecast = "Monthly Forecast"
    		  Predict = "Predicted Income"
    		  TotalDiffPredict = 'Actual Amount Difference from 
                                    Predicted'
    		  PctDiffPredict = 'Actual Pct Diff from Predicted';
    run;
    
    libname pgxl clear;
    
    proc sort data=prd_sales_final;
    	by Country;
    run;
    
    proc print data=work.prd_sales_final(obs=20);
    run;


    Here is what the code does:

    1. makes a connection to the prdsales_countries.xlsx Excel workbook
    2. prepares the Excel data using SAS and creates a final SAS table named prd_sales_final in the WORK library
    3. previews the SAS table using the PRINT procedure

  2. Highlight the Step 3 code and click Run.

  3. View the results.

    1. Confirm that the results show the first 20 rows of the SAS table.
    2. The printed results show that the SAS table contains detailed data.

Step 4: Create an Excel report.

  1. Quickly review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    ******************************;
    * Set file name and formats  *;
    ******************************;
    * Create a dynamic file name by using the current date *;
    %let currDate = %sysfunc(today(), yymmdd10.);
    %let fileName = xls01d01_&currDate..xlsx;
    
    
    * Create the Formats *;
    %let Blue=CX33a3ff;
    %let LightRed=cxFFA0A0;
    %let LightGreen=cx99FFBB;
    proc format;
    	* Set color for negative and positive values *;
    	value ColorFmt
    		low - 0 = "&LightRed"
    		0 <- high = "&LightGreen";
    		
    	* Bold negative values *;
    	value WeightFmt
    		low - 0 = "bold";
    quit;
    
    *********************Start Excel Report*******************;
    
    ***********************;
    * Create Excel report *;
    ***********************;
    ods _all_ close;
    ods excel file = "&outpath/&fileName"
              author = "Peter S"
              title = "Company Sales Overview"
              keywords = "Sales, Country Sales, Yearly Sales, Business 
                          Sales, Company Sales"
              nogtitle
              options(sheet_interval='none' 
                      embedded_titles='on' 
                      autofilter='all')
              style=Daisy;
    
    
    ***********;
    * Sheet 1 *;
    ***********;
    ods excel options(sheet_name='Company Analysis' 
                       frozen_headers='2');   
    			
    * Worksheet title *;			
    title height=18pt "Company Overview";
    title2 " ";
    
    * Bar plot *;
    title3 justify=left "Company Sales by Year";
    proc sgplot data=work.prd_sales_final;
    	vbar Year / 
    		response = Actual
    		fillattrs=(color=&Blue)
    		nooutline;
    	xaxis display=(nolabel);
    	yaxis display=(nolabel);
    run;
    title;
    
    * Table *;
    title justify=left "Company Actual vs Predicted Sales by Year";
    proc print data=pg.yearly_sales label noobs;
    	var Year Actual Predict ;
    	var DiffFromPredict ForecastAccuracy / 
                      style(data)=[backgroundcolor=ColorFmt.           
    	                          fontweight=WeightFmt.];
    	sum Actual Predict;
         sum DiffFromPredict / 
                      style(grandtotal)=[backgroundcolor=ColorFmt.];  
    run;
    title;
    
    * Line plot *;
    title justify=left "Product Sales by Year";
    proc sgplot data=work.prd_sales_final;
    	vline Year / 
    		response = Actual
    		group=Product
    		markers markerattrs=(symbol=circleFilled)
    		lineattrs=(thickness=1pt pattern=solid)
    		curvelabel;
    	xaxis display=(nolabel);
    	yaxis display=(nolabel);
    	styleattrs datacontrastcolors=(blue green orange purple);
    run;
    title;
    
    
    ***********;
    * Sheet 2 *;
    ***********;
    ods excel options(sheet_name='Country Analysis' 
                      sheet_interval='now' 
                      autofilter='all' 
                      frozen_headers='2'
    				  suppress_bylines='on' 
    				  absolute_column_width='20,20,20,20');
    	
    * Worksheet title *;	
    title height=18pt "Country Analysis";
    title2 " ";	
    
    * Table *;
    title3 justify=left "Country Sales by Year";
    proc print data=pg.country_sales noobs;
    	var Country Year Actual Predict;
    	var DiffFromPredict ForecastAccuracy / 
                      style(data)=[backgroundcolor=ColorFmt.           	                                               
                                   fontweight=WeightFmt.];
    	sum Actual Predict;
    	sum DiffFromPredict / 
                      style(grandtotal)=[backgroundcolor=ColorFmt.];  
    run;
    title;
    
    * Line plot *;
    title justify=left "Sales by Country";
    proc sgplot data=work.prd_sales_final;
    	vline Year / 
    		response=Actual
    		group=Country
    		markers markerattrs=(symbol=circlefilled)
    		lineattrs=(thickness=1pt pattern=solid)
    		curvelabel;
    	styleattrs datacontrastcolors=(red green blue);
    	xaxis display=(nolabel);
    	yaxis display=(nolabel);
    run;
    title;
    
    title justify=left "#byval1 Yearly and Product Analysis"; 
    proc report data=work.prd_sales_final
                spanrows;
    	* Create BY groups *;
    	by Country;
    	
    	* Select columns for the report *;
    	columns ('Company Sales Overview' Country Year Product 
                   Actual);
    	
    	* Specify what each column does in the report *;
    	define Country / 
                group style( column)=[fontweight=bold fontsize=10pt];
    	define Year / group;
    	define Product / group;
    	define Actual / analysis sum ;
    	
    	* Break after each group summarization *;
    	break after Country / summarize suppress 
                               style=[fontweight=bold fontsize=12pt];
    	compute after Country;
    		Country = 'Grand Total';
    	endcomp;
    	
    	break after Year / summarize suppress 
                            style=[fontweight=bold fontsize=11pt];
    	compute after Year;
    		Product = 'Total';
    	endcomp;
    run;
    title;
    
    ods excel close;


    Here is what the code does:

    1. creates an Excel workbook named xls01d01_<current date>.xlsx in the output folder of this course
    2. adds Excel properties and styles to the workbook
    3. uses a variety of SAS procedures to create multiple worksheets in the Excel workbook that contain printed results and visualizations

  2. Highlight the Step 4 code and click Run.

  3. Check the log to confirm that the code ran successfully.

Step 5: Download and open the Microsoft Excel workbook.

  1. Go to the main course folder and open the output folder. Download and open the xls01d01_<DDMMMYYYY>.xlsx Excel workbook. The Excel workbook name ends with today's date. 

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Notice the following about the Excel workbook:

    1. It contains two worksheets, one named Company Analysis and the other named Country Analysis.
    2. The Company Analysis worksheet begins with the title Company Overview, and the headers are frozen. The remaining worksheet contains a bar chart, a table with color formatting and filters, and a line plot.
    3. The Country Analysis worksheet begins with the title Country Analysis, and the headers are frozen. The remaining worksheet contains a table with color formatting, a line plot, and three tables with yearly analysis for each product. Each table is for the specific country. Notice the total under each year, and also notice that subtotal and grand total values are bolded.

  3. Close the Excel workbook.

Working with SAS® and Microsoft Excel
Lesson 01, Section 2 Demo: Importing Excel Data Into SAS Using PROC IMPORT

This demonstration illustrates how to use the IMPORT procedure to import a worksheet or range of cells from a Microsoft Excel workbook into SAS as a table.

Setup

Open the xls01d02.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Download and open the Microsoft Excel workbook.

  1. Download and open the prdsales_countries.xlsx from the data folder. Notice the following about the Excel workbook:

    1. It contains three worksheets named USA, Canada, and Mexico.
    2. The first worksheet is named USA.  
    3. The column heading Manager ID contains a space in each worksheet.
    4. The USA worksheet has 20,737 rows.

  2. Close the prdsales_countries.xlsx file.

Step 2: Import the Excel workbook as a SAS table.

  1. Review the code in Step 2 of the program.

    Open code or syntax in a separate window.

    * Best practice is to specify the VALIDVARNAME=V7 option to convert  
      column names to valid SAS column names *;
    options validvarname=v7; 
    
    * Import the Excel workbook *;
    proc import datafile="&path/data/prdsales_countries.xlsx" 
                dbms=xlsx 
                out=work.prdsales_usa
                replace;
    run; 

    1. The OPTIONS statement sets the VALIDVARNAME= option to V7. This option specifies that the column names must follow SAS rules and replaces all special characters with an underscore. It's best practice to always use this option to avoid column names with special characters. SAS Studio sets VALIDVARNAME= to the PREFERENCES setting value before each code submission. Using this with Excel is helpful because Excel doesn't have any rules regarding column names.
      Note: To avoid always having to use the OPTIONS statement, you can change the default setting in SAS.
    2. The IMPORT procedure uses the DATAFILE= option to import the prdsales_countries.xlsx Microsoft Excel workbook from the data folder to SAS as a table.
    3. The DBMS= option specifies to use the XLSX data source identifier.
    4. The OUT= option specifies to name the SAS table prdsales_usa and places it in the Work library.
    5. The REPLACE option replaces the SAS table if it already exists.

  2. Highlight the Step 2 code and click Run.

  3. Review the log.

    1. In the log, the first note indicates that a variable name changed from Manager ID to Manager_ID. This is done because the VALIDVARNAME= option that is specified in the OPTIONS statement replaces special characters in a column name with underscores.
    2. The second note in the log indicates that variables were converted because the data type is not supported by the V9 engine. This is because Excel supports VARCHAR data types, and the SAS V9 engine doesn't. SAS converts VARCHAR data types to CHAR with a fixed length.
    3. Lastly, the log shows that the imported data set has 20736 observations and 12 variables. This matches what we expect from the USA worksheet.

  4. Review the output data. The OUTPUT DATA tab shows a preview of the new SAS table. Notice that only the USA rows were imported. By default, the IMPORT procedure imports the first worksheet if one is not specified.

Step 3: Use the SHEET= statement option to import a specific worksheet as a SAS table.

  1. Review the code in Step 3 of the program. 

    Open code or syntax in a separate window.

    options validvarname=v7;
    proc import datafile="&path/data/prdsales_countries.xlsx" 
                dbms=xlsx 
                out=work.prdsales_usa
                replace;
        sheet='USA';
    run;
    
    * Preview the new SAS table *;
    proc contents data=work.prdsales_usa;
    run;
    
    proc print data=work.prdsales_usa(obs=10);
    run;

    1. The OPTIONS statement is required each time that data is imported from Excel if you want to comply with SAS column name requirements.
    2. The SHEET= statement option was added to the IMPORT procedure to specify to import the USA worksheet explicitly.
    3. The CONTENTS and PRINT procedures preview the new SAS table.

  2. Highlight the Step 3 code and click Run.

  3. Review the log. Notice that the log for the IMPORT procedure is the same as in the previous step.

  4. Review the results.

    1. The CONTENTS procedure shows a variety of information about the SAS table. The IMPORT procedure determines the type, length, format, informat, and label of the columns.
    2. The PRINT procedure prints the first 10 rows of the SAS table to preview the data.

Step 4: Use data set options with the IMPORT procedure.

  1. Review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    options validvarname=v7; 
    proc import datafile="&path/data/prdsales_countries.xlsx"
                dbms=xlsx 
                out=work.prdsales_ds_options (where=(State in
                                                       ('California',
                                                        'New York')))
                replace;
        sheet='USA';
    run;
    
    ...   

    1. In the IMPORT procedure, you can use data set options in the OUT= option. Here only the rows in the USA worksheet where State is California or New York are written out to the SAS table named prdsales_ds_options in the Work library. Using data set options here avoids having to write an additional DATA step after you import the data.
    2. The CONTENTS procedure shows a variety of information about the SAS table.
    3. The FREQ procedure is executed on the prdsales_ds_options table to confirm that only the California and New York rows were written out to the table.

  2. Highlight the Step 4 code and click Run.

  3. View the log.

    1. In the log, the same notes are shown for the IMPORT procedure that you saw earlier in this demo.
    2. Notice that the IMPORT procedure still imported all 20,736 rows from the USA worksheet to SAS even though data set options were used.

  4. View the results.

    1. The CONTENTS procedure indicates that the SAS table contains 3,456 rows. This means that all the rows were brought to SAS and then filtered with the data set option that we specified.
    2. The FREQ procedure confirms that only the rows with California or New York were written out to the SAS table prdsales_ds_options.

Step 5: Import a specific range of data from an Excel worksheet.

  1. Review the code in Step 5 of the program.

    Open code or syntax in a separate window.

    options validvarname=v7; 
    proc import datafile = "&38;path/data/prdsales_countries.xlsx"
                dbms=xlsx 
                out=work.prdsales_usa_range
                replace;
    	sheet='USA';
    	range='A1:D10'; *<----Specify a range or named range *;
    run;
    
    ... 

    1. The RANGE statement in the IMPORT procedure enables you to specify a range of cells (or a named range) to import to SAS. Here, the cells A1 through D10 will be imported into SAS.
    2. The new SAS table is named prdsales_usa_range in the Work library.
    3. The PRINT procedure prints the entire prdsales_usa_range SAS table.

  2. Highlight the Step 5 code and click Run.

  3. View the log. Notice that only nine observations and four variables were imported from the USA worksheet.

  4. View the results. The PRINT procedure prints the entire table. Notice that only the small, selected range of cells are in the new SAS table.

Working with SAS® and Microsoft Excel
Lesson 01, Section 2 Demo: Exporting SAS Data into Excel Using PROC EXPORT

This demonstration illustrates how to use the EXPORT procedure to export a SAS table to a Microsoft Excel workbook.

Setup

Open the xls01d03.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Preview the SAS table.

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    proc contents data=pg.prdsales_final;
    run;
    
    proc print data=pg.prdsales_final(obs=10);
    run; 

    1. The CONTENTS and PRINT procedures preview the SAS table pg.prdsales_final.

  2. Highlight the Step 1 code and click Run.

  3. View the results.

    1. In the results of the CONTENTS procedure, notice that many of the columns contain formats and labels.
    2. Notice that the PRINT procedure applies formats, not labels, to the columns by default.

Step 2: Export the SAS table to Excel.

  1. Review the code in Step 2 of the program.

    Open code or syntax in a separate window.

    proc export data=pg.prdsales_final
                dbms=xlsx 
                outfile="&outpath/xls01d03_export.xlsx"
                replace;
        sheet='Raw Data';
    run;  

    1. The EXPORT procedure exports the SAS table to Excel.
    2. The DATA= option specifies the SAS table pg.prdsales_final to export.
    3. The DATA= option specifies the SAS table pg.prdsales_final to export.
    4. The OUTFILE= option specifies the path and name of the Excel workbook to create. Here, the xls01d03_export.xlsx workbook will be created in the output folder.
    5. The REPLACE option replaces the Excel workbook if it already exists.
    6. The SHEET= statement option names the new worksheet Raw Data.

  2. Highlight the Step 2 code and click Run.

  3. View the log. Confirm that the xls01d03_export.xlsx file was created successfully.

Step 3: Download and open the Excel workbook.

  1. Download and open the xls01d03_export.xlsx file from the output folder. Notice the following:

    1. All data was exported to the Excel workbook into a single worksheet named Raw Data.
    2. The column names do not include labels.
    3. No column formats were applied in the Excel workbook.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 4: Apply column labels to the Excel workbook.

  1. Review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    proc export data=pg.prdsales_final 
                dbms=xlsx 
                outfile="&outpath/xls01d03_export.xlsx"
                replace
                label; *<-------Apply column labels*;
        sheet='Column Labels';
    run;

    1. The LABEL option specifies to write column label names to the Excel workbook.
    2. The SHEET= statement option creates a new worksheet named Column Labels in the xls01d03_export.xlsx Excel workbook.

  2. Highlight the Step 4 code and click Run.

  3. View the log. Confirm that the xls01d03_export.xlsx file was created successfully.

Step 5: Download and open the Excel workbook.

  1. Download and open the xls01d03_export.xlsx from the output folder and select the Column Labels worksheet. Notice the following:

    1. A new sheet named Column Labels was created in the existing Excel workbook.
    2. The column names in the new worksheet use column labels.
    3. No column formats were applied.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

  3. Go to the course folder and expand the output folder. Notice that a xls01d03_export.xlsx.bak file exists. SAS creates a .bak file (a backup copy of the original file) before updating the workbook. These .bak files are not deleted when the update to the Excel workbook is made. You can delete the .bak file.

Step 6: Preserve SAS formats.

  1. Review the code in Step 6 of the program.

    Open code or syntax in a separate window.

    data work.prdsales_format;
        set pg.prdsales_final;
        Manager_ID_Format = put(Manager_ID,z10.);
    run;
    
    proc export data=work.prdsales_format
                dbms=xlsx
                outfile="&outpath/xls01d03_export.xlsx"
                replace;
        sheet='Add Format';
    run;  

    1. The DATA step creates a new temporary table named prdsales_format from the pg.prdsales_final SAS table. A common drawback associated with using the EXPORT procedure is the inability to preserve SAS formats in Excel. One method to preserve the SAS formats is to convert the numeric column to a character in SAS. Here, the assignment statement uses the PUT function to convert the values of Manager_ID, a numeric column, into a character string applying the Z10 format.
    2. The EXPORT procedure exports the new SAS table prdsales_formats to Excel as a new worksheet named Add Format in the xls01d03_export.xlsx Excel workbook.

  2. Highlight the Step 6 code and click Run.

  3. View the log. Confirm that the xls01d03_export.xlsx file was created successfully.

  4. View the output data. In the OUTPUT DATA tab find the Manager_ID_Format column. Notice that the Z10 format was applied.

Step 7: Download and open the Excel workbook.

  1. Download and open the xls01d03_export.xlsx Excel file from the output folder. Notice the following.

    1. A new worksheet named Add Format was added to the Excel workbook.
    2. The Manager_ID_Format column has been added as the last column in the worksheet with the SAS Z10 format applied.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Working with SAS® and Microsoft Excel
Lesson 01, Section 3 Demo: Importing and Exporting Data Using the LIBNAME Statement

This demonstration illustrates how to use the LIBNAME engine to make a direct connection to a Microsoft Excel workbook.

Setup

Open the xls01d04.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Make a LIBNAME connection to the Excel workbook.

  1. Review the code in Step 1 of the program.

  2. Open code or syntax in a separate window.

    options validvarname=v7;
    libname pgxl xlsx "&path/data/prdsales_countries.xlsx" 
                       access=readonly;

    1. The OPTIONS statement is used to specify SAS options. The VALIDVARNAME= system option controls the type of SAS column names that can be used or created during a SAS session. The V7 value indicates that a column name is changed to a valid SAS name, replacing all special characters with underscores.
    2. The LIBNAME statement associates a SAS libref with a Microsoft Excel workbook. This LIBNAME statement specifies pgxl as a libref, the engine name XLSX specifies an engine that supports the connection to the specified Microsoft Excel workbook in the .xlsx file format, and the ACCESS=READONLY option assigns a Read-Only attribute to an entire SAS library. Using the ACCESS= option avoids overwriting the Excel workbook by mistake.

  3. Highlight the Step 1 code and click Run.

  4. View the log. Confirm that the pgxl libref was successfully assigned to the prdsales_countries.xlsx Excel workbook.

Step 2: View the SAS library.

  1. Review the code in Step 2 of the program.

    Open code or syntax in a separate window.

    * View Available Tables in the pgxl Library programmatically *;
    options validvarname=v7;  
    proc contents data=pgxl._all_;
    run;

    1. The OPTIONS statement is used to specify rules for valid SAS column names. This option is required every time that you reference the pgxl libref in SAS Studio because the default value in Studio is set to ANY. You can change this globally in the preferences in SAS Studio if you want.
    2. The CONTENTS procedure with the libref pgxl and keyword _ALL_ shows the contents of the entire SAS library.

  2. Highlight the Step 2 code and click Run.

  3. View the log. The note indicates that the Manager ID column was changed to Manager_ID for each worksheet.

  4. View the results.

    1. The first set of results shows information about the SAS library, like the libref name, pgxl, and the engine used, XLSX. It also shows additional information like the access to the library and the physical name of the file. You can also see that the SAS library contains three members, CANADA, MEXICO, and USA. Those are the three worksheets in the prdsale_countries.xlsx Excel workbook.
    2. Notice that in the results of the CONTENTS procedure the number of observations in each worksheet cannot be determined through the LIBNAME connection.
    3. The remaining results show general information and column metadata for each worksheet in Excel. Notice that the XLSX engine determines the column type, length, format, informat, and label for each column.

  5. You can also view information about the library manually by selecting Libraries and expanding the pgxl library. Notice that the CANADA, MEXICO, and USA tables are available through the pgxl library reference to the Excel workbook.

Step 3: Work with the worksheets through the SAS library.

  1. Review the code in Step 3 of the program.

    Open code or syntax in a separate window.

    options validvarname=v7;
    proc print data=pgxl.mexico(obs=10);
    run;
    
    proc print data=pgxl.canada(obs=10);
    run;
    
    proc print data=pgxl.usa(obs=10);
    run; 

    1. The PRINT procedures use the pgxl library reference to print 10 rows from each Excel worksheet without making a copy of the Excel data in SAS.

  2. Highlight the Step 3 code and click Run.

  3. View the log. Notice that the column name Manager ID was changed to Manager_ID in SAS in each procedure.

  4. View the results. The results show the first 10 rows of each worksheet from the Excel workbook without making a copy of the data in SAS.

  5. Highlight only the last PRINT procedure. To see what happens if you run a procedure using the pgxl library without setting the VALIDVARNAME=V7 option, click Run.

  6. View the log and the results. The Manager ID column was not renamed because the VALIDVARNAME= option was not specified. In this case, the default value was used. The default value of the VALIDVARNAME= option in SAS Studio is ANY. ANY allows any characters in column names. The VALIDVARNAME= option is reset to the default every time that the code is run in SAS Studio.

Step 4: Use the DATA Step to concatenate the Excel worksheets as a SAS table.

  1. Review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    options validvarname=v7;
    
    data work.prd_all_libname;
        length Country $12. State $40.;
        set pgxl.usa
            pgxl.canada
            pgxl.mexico;
        ...
    run;
    ... 

    1. The OPTIONS statement sets the VALIDVARNAME= option to V7 to comply with the naming conventions for SAS columns.
    2. The DATA step creates a new SAS table named prd_all_libname in the Work library. The new DATA step uses the SET statement and concatenates each worksheet in the prdsales_countries.xlsx Excel file using the SAS library name pgxl that was set earlier. A variety of other statements are used to prepare the data.
    3. In the DATA step, the LENGTH statement specifies a length for the Country and State columns to avoid truncation because the column lengths differ in each worksheet.
    4. The FORMAT statement modifies the format of the columns. The _CHARACTER_ keyword removes the formats from all character columns to avoid truncation. This is used because the default format of character columns is the length of the column. This can cause truncation issues when concatenating tables.
    5. The LABEL statement adds a label to the Manager_ID column.
    6. The CONTENTS, PRINT, and FREQ procedures preview the new SAS table.

  2. Highlight the Step 4 code and click Run.

  3. View the log. Confirm that the Manager ID column was changed to Manager_ID and the DATA step created the new SAS table prd_all_libname with 34,560 observations and 13 variables.

  4. View the results.

    1. The CONTENTS procedure displays table information and the column metadata for the new SAS table. View the Alphabetic List of Variables and Attributes table. Notice that the XLSX engine automatically determines data types, length, and informats if not specified in the DATA step. The DATA step changed the Country and State column lengths to avoid truncation when concatenating tables. The DATA step also removed all character formats to avoid any truncation issues. By default, the label of the column is the original column label in Excel, which includes special characters.
    2. The PRINT procedure previews the first 10 rows of the new SAS table.
    3. The FREQUENCY procedure creates a one-way frequency of the Country and State columns to confirm that the SAS table contains the Canada, Mexico, and USA data and no truncation issues exist in either column. This is good practice when concatenating tables to confirm that no truncation issues occurred.

Step 5: Write out the SAS table as a new Excel workbook.

  1. Review the code in Step 5 of the program.

    Open code or syntax in a separate window.

    * Create a new Excel workbook named prdsales_libname_engine.xlsx *;
    libname outxl xlsx "&outpath/xls01d04.xlsx";
    
    data outxl.prdsales_all;
         set work.prd_all_libname;
    run;
    
    * Clear connection *;
    libname outxl clear;

    1. The LIBNAME statement creates a new Excel workbook named xls01d04.xlsx with the libref outxl using the XLSX engine.
    2. The DATA step uses the libref outxl from the LIBNAME statement to write the SAS table prd_all_libname to the newly created Excel workbook xls01d04.xlsx. The worksheet will be named prdsales_all.
    3. The last LIBNAME statement clears the connection to the new Excel workbook. It's best practice to do this when you are done with the file.
      Note: This step could have been included in the DATA step in the previous step to write directly to an Excel workbook without creating a temporary SAS table first.

  2. Highlight the Step 5 code and click Run.

  3. View the log. Confirm that the code ran successfully.

  4. Navigate to the output folder and confirm that the xls01d04.xlsx Excel workbook was created.

Step 6: Download and open the Excel workbook.

  1. Download and open the xls01d04.xlsx Microsoft Excel file from the output folder. Notice the following about the Excel workbook:

    1. It contains all the data from the SAS table.
    2. The new worksheet name is prdsales_all.
    3. The column names do not include labels.
    4. No column formats were applied.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 7: Add three worksheets to the existing Excel workbook.

  1. Review the code in Step 7 of the program.

    Open code or syntax in a separate window.

    libname outxl xlsx "&outpath/xls01d04.xlsx";
    
    data outxl.USA;
        set outxl.prdsales_all;
        where Country = 'USA';
        ...
    run;
    
    data outxl.Canada;
        set outxl.prdsales_all;
        where Country = 'Canada';
        ...
    run;
    
    data outxl.Mexico;
        set outxl.prdsales_all;
        where Country = 'Mexico';
        ...
    run;
    
    libname outxl clear;  

    1. The first LIBNAME statement connects to the xls01d04.xlsx Excel workbook that was created in the previous DATA step.
    2. Each of the three DATA steps create a new worksheet in the existing Excel workbook using the prdsales_all worksheet in the xls01d04.xlsx workbook. The LIBNAME engine enables you to read from and write to the same Excel workbook. Here, the DATA statement uses the outxl libref to create each new worksheet, and the SET statement uses the outxl libref to read from the Excel worksheet. Each worksheet will contain data for the specific country and the conversion to the native currency. Using this method does not require an intermediary SAS table.
    3. The last LIBNAME statement clears the connection to the Excel workbook.

  2. Highlight the Step 7 code and click Run.

  3. View the log. Confirm that the code ran successfully.

Step 8: Download and open the Excel workbook.

  1. Download and open the xls01d04.xlsx Microsoft Excel file from the output folder. Notice the following about the Excel workbook:

    1. The Excel workbook now contains four worksheets: prdsales_all, USA, Canada, and Mexico.
    2. The USA, Canada and Mexico worksheets contain the currency conversion column for the respective countries.
    3. No column formats or labels were applied.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 9: Delete the .bak file.

  1. Go to the output folder and confirm that the xls01d04.xlsx.bak file exists.

  2. Review the code in Step 9 - a of the program.

    Open code or syntax in a separate window.

    * Reference the Excel workbook .bak file *;
    filename f_bak "&outpath/ xls01d04.xlsx.bak";
    
    * The FEXISTS function indicates if the file exists *;
    data work.files;
        f_bak_exists = fexist('f_bak');
    run;

    1. The FILENAME statement associates the external Excel .bak file with the file reference name f_bak.
    2. The DATA step checks to see whether the f_bak file exists and creates a column named f_bak_exists. If the file exists, a value of 1 is shown, indicating that the xls01d04.xlsx.bak file does exist.

  3. Highlight the Step 9 - a code and click Run.

  4. View the log. Confirm that the code ran successfully.

  5. View the output data to confirm that the f_bak_exists column has a value of 1. This indicates that the xls01d04.xlsx.bak file exists.

  6. Review the code in Step 9 - b of the program.

    Open code or syntax in a separate window.

    * If the files exists, delete using the FDELETE function *;
    data _null_;
        if fexist('f_bak') = 1 then do;
             if_bak_exists = fdelete('f_bak');
             put 'NOTE: The file was deleted';
        end;
    run; 

    1. The DATA step that has _NULL_ in the DATA statement does not create a SAS table. Instead, it only runs the code.
    2. Here, the IF statement identifies whether the f_bak file exists. If the file exists, the IF statement uses the FDELETE function to delete the xls01d04.xlsx.bak file. It also prints the following note in the log using the PUT statement: The file was deleted.

  7. Highlight the Step 9 - b code and click Run.

  8. View the log. Confirm that the note The file was deleted is shown in the log.

  9. Open the output folder. Notice that the xls01d04.xlsx.bak file was successfully deleted.

Lesson 02


Working with SAS® and Microsoft Excel
Lesson 02, Section 1 Demo: Using the ODS EXCEL Statement to Create Excel Reports

This demonstration illustrates how to use the ODS EXCEL statement to create simple Microsoft Excel reports.

Setup

Open the xls02d01.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Create a simple Microsoft Excel report.

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d01a_ODS_Excel_Basics.xlsx";
    
    proc print data=pg.prdsales_final(obs=10);
    run;
    
    ods excel close;
    

    1. The ODS EXCEL statement with the FILE= option opens the ODS destination to Excel, which creates an Excel workbook. The Excel workbook is named xls02d01a_ODS_Excel_Basics.xlsx and placed in the output folder.
    2. The PRINT procedure prints 10 rows from the prdsales_final SAS table in the pg library to the Excel workbook.
    3. The ODS EXCEL CLOSE statement closes the destination to Excel.

  2. Highlight the Step 1 code and click Run.

  3. View the log. View the last note and confirm that it writes to the Excel file xls02d01a_ODS_Excel_Basics.xlsx.

  4. View the results. Notice that even though you write to an Excel file, the default HTML results are still shown in SAS.

Step 2: Download and open the Excel workbook.

  1. Download and open the xls02d01a_ODS_Excel_Basics.xlsx file from the output folder. Notice the following:

    1. The PRINT procedure exported the SAS results to the Excel workbook and applied all SAS formatting.
    2. The worksheet is named Print 1 - Data Set PG.PRDSAL by default.
    3. The column labels were not applied in the Excel output.

  2. Close the Excel file.

Step 3: Close the SAS results.

  1. Review the code in Step 3 of the program.

    Open code or syntax in a separate window.

    * Close the default SAS output destination *;
    ods _all_ close;
    
    ods excel file= "&outpath/xls02d01b_CloseDefault.xlsx";
    
    proc print data=pg.prdsales_final(obs=10);
    run;
    
    ods excel close;
    
    *********************************************************;
    * This is required for the SAS Windowing Environment    *;
    *********************************************************;
    * Run the ODS HTML statement to turn on the default     *; 
    * SAS destination.                                      *;
    *********************************************************;
    /* ods html; */  

    1. The ODS _ALL_ CLOSE statement closes the default SAS destination. This avoids any issues that might arise from printing a large table in the SAS results.
    2. The ODS EXCEL statement opens only the SAS EXCEL destination to create an Excel file.
    3. The PRINT procedure prints 10 rows from the prdsales_final SAS table in the pg library to the Excel workbook.
    4. If you are using the SAS windowing environment, you must uncomment the ODS HTML statement and run the statement. By default, SAS Studio and SAS Enterprise Guide turn on the default SAS HTML destination. The SAS windowing environment does not.

  2. Highlight the Step 3 code and click Run.

  3. View the results. Notice that no results are shown in SAS. The ODS _ALL_ CLOSE statement disables the default SAS destination.

  4. View the log. The log shows that the Excel file was created successfully even though the default SAS destination was closed.

  5. View the output folder. Confirm that the xls02d01b_CloseDefault.xlsx Excel file was created.

Step 4: Modify the Excel workbook properties.

  1. Review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d01c_ExcelProperties.xlsx"
              title='This is the title of the Excel workbook'
    	  author='Peter S'
    	  category='Sales Overview for the Team'
    	  status='Updated Version'
    	  comments='These are the comments I want to add to the 
                        workbook.'
              keywords='Sales, Revenue, Predict, Actual';
    
    proc print data=pg.prdsales_final(obs=10);
    run;
    
    ods excel close;  

    1. The ODS EXCEL statement includes a variety of options to modify the Excel workbook properties.
    2. The TITLE= option specifies a title for the Excel file.
    3. The AUTHOR= option specifies the author of the Excel file.
    4. The CATEGORY= option specifies the category of the Excel file.
    5. The STATUS= option inserts the status of the Excel file.
    6. The COMMENTS= option adds comments to the Excel file.
    7. The KEYWORDS= option adds keywords to the Excel file.

  2. Highlight the Step 4 code and click Run.

  3. View the log. Confirm that the Excel file xls02d01c_ExcelProperties.xlsx was created.

Step 5: Download and open the Excel workbook.

  1. Download and then open the xls02d01c_ExcelProperties.xlsx file from the output folder.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. In the Excel workbook, select File > Info. Notice that the Properties section on the right size of the Excel workbook have been modified.

  3. Click the drop-down arrow in the Properties section on the right side of the Excel workbook, and then select Advanced Properties. Notice that the advanced properties of the Excel file have also been modified.

  4. Close the Excel file.

Step 6: Create a dynamic Excel file name.

  1. Review the code in Step 6 – a of the program.

    Open code or syntax in a separate window.

    %let currentDate = %sysfunc(today(),yymmdd10.);
    %put &=currentDate; 

    1. The %LET statement creates a macro variable named currentDate that stores the current date in YYMMDD10 format.
    2. The %PUT statement displays the value of the currentDate macro variable in the log.

  2. Highlight the Step 6 – a code and click Run.

  3. View the log. In the log, the value of the currentDate macro value displays today's date.

  4. Review the code in Step 6 – b of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d01d_&currentDate..xlsx";
    ...
    ods excel close; 

    1. In the FILE= option, the name of the Excel worksheet is specified as xls02d01d_, followed by the value of the currentDate macro variable. The first period after the currentDate macro variable ends the macro variable value, and the second period is added followed by the file extension.
    2. The PRINT procedure prints 10 rows to the Excel workbook.

  5. Highlight the Step 6 – b code and click Run.

  6. View the log. The last note in the log displays the new Excel file name xls02d01d_ with the current date appended at the end of the file name.

  7. View the contents of the output folder. Notice that this folder contains the Excel file xls02d01d_<current date>.xlsx. This method enables you to easily create dynamic Excel file names.

Step 7: Use multiple procedures with the ODS EXCEL destination.

  1. Review the code in Step 7 – a of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d01d_&currentDate..xlsx";
    
    proc print ...
    run;
    
    proc freq ...
    run;
    
    proc means ...
    run;
    
    proc sgplot ...
    run;
    
    ods excel close; 

    1. The ODS EXCEL statement creates a file named xls02d01d_<current date>.xlsx.
    2. Within the ODS EXCEL statement are the following four procedures: PRINT, FREQ, MEANS, and SGPLOT.

  2. Highlight the Step 7 code and click Run.

  3. View the log. Confirm that the Excel file was created successfully.

  4. View the results. The results show that four tables and a visualization were created.

Step 8: Download and open the Excel workbook.

  1. Download and open xls02d01d_<current date>.xlsx from the output folder. Notice the following:

    1. The new Excel workbook contains five worksheets, one for each of the four tables and one for the visualization. By default, ODS EXCEL creates a worksheet for each output.
    2. Each worksheet tab is named by default using the SAS procedure that created it.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Working with SAS® and Microsoft Excel
Lesson 02, Section 2 Demo: Modifying the Excel Worksheet Output, Names, and Colors

This demonstration illustrates how to use the ODS EXCEL statement to control how output is sent to worksheets and modify a worksheet's name and color.

Setup

Open the xls02d02.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Create an Excel report with multiple procedures.

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d02_Worksheets.xlsx";
    
    proc print ...
    run;
    
    proc freq ...
    run;
    
    proc means ...
    run;
    
    proc sgplot ...
    run;
    
    ods excel close; 

    1. The ODS statement creates an Excel file named xls02d02_Worksheets.xlsx.
    2. Within the ODS statements, the PRINT, FREQ, MEANS, and SGPLOT procedures are used.

  2. Highlight the Step 1 code and click Run.

  3. View the log. Confirm that the xls02d02_Worksheets.xlsx file was successfully created.

  4. View the results. The SAS procedures create five outputs in the results: four tables and a single visualization.

Step 2: Download and open the Excel workbook.

  1. Download and open the xls02d02_Worksheets.xlsx file from the output folder. Notice the following:

    1. The new Excel workbook contains five worksheets, one for each table and one for the visualization, even though four SAS procedures were used. That is because, by default, each specific output is sent to its own Excel worksheet.
    2. By default, each worksheet tab is named by the procedure used to create it.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 3: Manually specify when to create a worksheet.

  1. Review the code in Step 3 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d02_Worksheets.xlsx"
              options(sheet_interval = 'none');         
    
    * Worksheet 1 *;
    proc print ...
    run;
    
    
    * Worksheet 2 *;
    ods excel options(sheet_interval = 'now');          
    
    proc freq ...
    run;
    
    proc means ...
    run;
    
    
    * Worksheet 3 *;
    ods excel options(sheet_interval = 'now');
    
    proc sgplot ...
    run;
    
    ods excel close; 


    1. Above each procedure or procedures, you can specify new options in another ODS EXCEL statement to modify the output for that procedure (or multiple procedures).
    2. The first SHEET_INTERVAL= suboption with the value 'NONE' turns off the creation of a new worksheet for each output.
    3. The SHEET_INTERVAL= suboption with the value 'NOW' creates a new worksheet for the procedures below the statement. The SHEET_INTERVAL= suboption was specified above the PRINT procedure, above the FREQ and MEANS procedures, and above the SGPLOT procedures to modify when to write out the results to Excel.
      Note: Available values in the SHEET_INTERVAL= suboption are 'BYGROUP' | 'PAGE' | 'PROC' | 'NONE' | 'NOW' | 'TABLE'.

  2. Highlight the Step 3 code and click Run.

  3. View the log. Confirm that the xls02d02_Worksheets.xlsx file was successfully created.

Step 4: Download and open the Excel workbook.

  1. Download and open xls02d02_Worksheets.xlsx from the output folder. Notice the following:

    1. The new Excel workbook contains three worksheets: one for the initial SHEET_INTERVAL='NONE' suboption, and one for each occurrence of the SHEET_INTERVAL='NOW' suboption.
    2. By default, each worksheet is named Sheet n.
    3. On Sheet 2, notice that the FREQ and MEANS procedure titles are shown before each output table.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 5: Modify worksheet names and the tab color.

  1. Review the code in Step 5 of the program.

    Open code or syntax in a separate window.

    ods noproctitle;
    
    ods excel file="&outpath/xls02d02_Worksheets.xlsx"
    	  options(sheet_interval = 'none'
    	          sheet_name = 'Data Preview'            
                      tab_color = 'red');                
    
    * 1 *;
    proc print ...
    run;
    
    
    * 2 *;
    ods excel options(sheet_interval = 'now'
                      sheet_name = 'Analysis'            
                      tab_color = 'blue');
    
    proc freq ...
    run;
    
    proc means ...
    run;
    
    * 3 *;
    ods excel options(sheet_interval = 'now'
                      sheet_name = 'Visualization'
                      tab_color = 'Green');             
     
    proc sgplot ...
    
    ods excel close;
    
    ods proctitle;

    1. The ODS NOPROCTITLE statement removes the procedure titles from the report. The ODS PROCTITLE statement at the end of the program sets the option back to the default.
    2. The SHEET_NAME suboption names the worksheet.
    3. The TAB_COLOR suboption adds a color to the worksheet tab.

  2. Highlight the Step 5 code and click Run.

  3. View the log. Confirm that the xls02d02_Worksheets.xlsx file was successfully created.

Step 6: Download and open the Excel workbook.

  1. Download and open the xls02d02_Worksheets.xlsx file from the output folder. Notice the following:

    1. The new Excel workbook contains the worksheets Data Preview, Analysis, and Visualization.
    2. The tab of each worksheet is colored.
    3. In the Analysis worksheet, the procedure titles were removed above each output table.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Working with SAS® and Microsoft Excel
Lesson 02, Section 2 Demo: Modifying the Appearance of Tables and Worksheets

This demonstration illustrates how to use ODS EXCEL statement options and suboptions to add a filter, titles, and footnotes and to freeze columns and rows when creating an Excel report.

Setup

Open the xls02d03.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Create a default Excel report with titles and footnotes.

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d03_Appearance.xlsx";
    
    * Worksheet 1 - Summary Table *;  
    title height=14pt "Actual vs Predicted Sales by Country and Year";
    footnote "Created on June 18th";
    proc print data=pg.country_sales noobs;
         sum Actual Predict DiffFromPredict;
    run;
    title;
    
    
    * Worksheet 2 - Visualization *;
    title height=14pt "Total Sales by Year";
    proc sgplot data=pg.country_sales;
         vbar Year /
              response=Actual
              fillattrs=(color=dodgerblue);
         yaxis display=(nolabel);
    run;
    title;
    footnote;
    
    ods excel close; 

    1. The ODS EXCEL statement creates a file named xls02d03_Appearance.xlsx.
    2. The PRINT procedure prints the entire table and calculates the total sum of the Actual, Predict, and DiffFromPredict columns.
    3. The SGPLOT procedure creates a visualization.
    4. TITLE and FOOTNOTE statements add a title and footnote to each procedure.

  2. Highlight the Step 1 code and click Run.

  3. View the log. Confirm that the xls02d03_Appearance.xlsx file was successfully created.

Step 2: Download and open the Excel workbook.

  1. Download and open xls02d03_Appearance.xlsx from the output folder. Notice the following:

    1. Two worksheets were created in the Excel workbook.
    2. In the first worksheet with the output table, no title or footnote is displayed even though a TITLE statement and a FOOTNOTE statement were specified prior to the PRINT procedure. This is because, by default, output tables do not display titles or footnotes in Excel.
    3. In the second worksheet with the visualization, the title and footnote are displayed within the image borders.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 3: Include titles and footnotes in the Excel workbook.

  1. Review the code in Step 3 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d03_Appearance.xlsx"
              nogtitle nogfootnote                  
              options(embedded_titles="on"           
                      embedded_footnotes="on");
    
    ...
    
    ods excel close;

    1. The NOGTITLE and NOGFOOTNOTE options print the title and footnote of the graphic outside of the graph border in an Excel cell.
    2. The EMBEDDED_TITLES= and EMBEDDED_FOOTNOTES= options specify to add titles and footnotes in table output.

  2. Highlight the Step 3 code and click Run.

  3. View the log. Confirm that the xls02d03_Appearance.xlsx file was successfully created.

Step 4: Download and open the Excel workbook.

  1. Download and open the xls02d03_Appearance.xlsx file from the output folder. Notice the following:

    1. In the first worksheet, the titles and footnotes are displayed above and below the output table in a cell in Excel.
    2. In the second worksheet, the title and footnotes are placed outside the graph borders in a cell in Excel instead of in the image itself.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 5: Add a filter, modify column widths, and freeze columns and rows.

  1. Review the code in Step 5 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d03_Appearance.xlsx"
              nogtitle nogfootnote                  
              options(embedded_titles="on"          
                      embedded_footnotes="on");     
    
    * 1 - Summary Table *; 
    ods excel options(autofilter='all'                     
                      absolute_column_width='15,10,20,20'   
                      frozen_headers='3'                    
                      frozen_rowheaders='2');              
    
    title ...
    footnote ...
    proc print ...
    run;
    title;
    
    
    * 2 - Visualization *;     
    ods excel options(frozen_headers='off'         
                      frozen_rowheaders='off');    
                                                
    title ...
    proc sgplot ...
    run;
    title;
    footnote;
    
    ods excel close; 

    1. The second ODS EXCEL statement above the PRINT procedure adds the AUTOFILTER=, ABSOLUTE_COLUMN_WIDTH=, FROZEN_HEADER=, and FROZEN_ROWHEADERS= suboptions. A second ODS EXCEL statement is not required, but it helps compartmentalize the code by adding options prior to each procedure.
    2. The AUTOFILTER= suboption turns on filtering for the specified columns in the worksheet.
    3. The ABSOLUTE_COLUMN_WIDTH= suboption specifies the column widths. Use a list of widths for columns instead of allowing SAS to determine the column width (measured widths). The number list is a comma-separated list of numbers. A value of 0 resets the default.
    4. The FROZEN_HEADERS= suboption specifies that headers can scroll or not scroll vertically. The value 3 places a freeze at row 3.
    5. The FROZEN_ROWHEADERS= suboption specifies whether the row headers on the left scroll when the table data scrolls from left to right. The value 2 places a freeze on column 2.
    6. The ODS EXCEL statement above the SGPLOT procedure turns off the frozen headers and row headers. This is because some options and suboptions continue to be applied to the remaining output until you turn them off or reset them. In this example, if you do not reset the FROZEN_HEADERS= and FROZEN_ROWHEADERS= suboptions, parts of the image in the visualization will be frozen.

  2. Highlight the Step 5 code and click Run.

  3. View the log. Confirm that the xls02d03_Appearance.xlsx file was successfully created.

Step 6: Download and open the Excel workbook.

  1. Download and open the xls02d03_Appearance.xlsx file from the output folder. Notice the following:

    1. In the first worksheet, a filter button has been placed on each column header.
    2. In the first worksheet, the column widths have been modified for the first four columns.
    3. In the first worksheet, scroll down in the table and see that a freeze has been placed at row 3. This enables you to always see the table column headers.
    4. In the first worksheet, scroll to the right and see that a freeze has been placed at column 2 (B) so that the Country and Year columns are always visible.
    5. In the second worksheet, the freeze was removed, so the visualization appears as expected.

      Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Working with SAS® and Microsoft Excel
Lesson 02, Section 2 Demo: Setting the Workbook Printing Options

This demonstration illustrates how to use ODS EXCEL suboptions to set the workbook printing options.

Setup

Open the xls02d04.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Use the REPORT procedure to create a SAS report.

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    * Sort the data *;
    proc sort data=pg.prdsales_final 
              out=final;
        by Country;
    run;
    
    * Create a report *;
    proc report data=final
                spanrows;
        * Create BY groups *;
        by Country;
        * Select columns for the report *;
        columns ('Company Sales Overview' Country Year Product Actual);
        * Specify what each column does in the report *;
        define Country / group;
        define Year / group;
        define Product / group;
        define Actual / analysis sum ;
        * Break after each group summarization *;
        break after Country / summarize suppress;
        break after Year / summarize suppress;
    run; 

    1. The SORT procedure sorts the pg.prdsales_final table by the Country column and creates a new table named work.final. The data is sorted to use the BY statement in the REPORT procedure.
    2. The REPORT procedure combines the features of the PRINT, MEANS, and TABULATE procedures with features of the DATA step in a single report-writing tool that can produce a variety of reports. The BY statement in the REPORT procedure creates a summarization for each BY group.

  2. Highlight the Step 1 code and click Run.

  3. View the log. Confirm that the program ran successfully.

  4. View the results. Notice that the REPORT procedure summarized the detailed data set and created a report for each value of Country. The report was summarized by Year and Product and includes subtotals and an overall total.

Step 2: Create an Excel report with default printing options.

  1. Review the code in Step 2 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d04_Print_Layout.xlsx"
              options(sheet_name='#byval1'        
                      suppress_bylines='on');
    
    proc report ...
    run;
    
    ods excel close; 

    1. The REPORT procedure can be used like the PRINT, MEANS, FREQ, and SGPLOT procedures to write out a report to Excel.
    2. The SHEET_NAME= suboption names the sheet based on the specific BY-group column.
    3. The SUPPRESS_BYLINES= suboption suppresses BY lines in the worksheet. BY lines are placed at the top of the table and specify the column name and the specific BY group. Suppressing them removes the extra information.

  2. Highlight the Step 2 code and click Run.

  3. View the log. Confirm that the xls02d04_Print_Layout.xlsx file was successfully created.

Step 3: Download and open the Excel workbook.

  1. Download and open the xls02d04_Print_Layout.xlsx file from the output folder. Notice the following:

    1. The worksheets were automatically named by using the BY groups: Canada, Mexico, and USA.
    2. The BY group has been suppressed above each output table.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Select File > Print. In the Settings section, select Print Entire Workbook from the drop-down list.

  3. On the right side of the screen, view the print preview. Notice the following:

    1. The table is aligned to the top left corner.
    2. The table contains color.
    3. The paper is in portrait orientation.
    4. There are three separate pages to print.

  4. Close the Excel file.

Step 4: Modify the print layout.

  1. Review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    %let currDate = %sysfunc(today(), weekdate.);
    %put &=currDate;
    
    ods excel file="&outpath/xls02d04_Print_Layout.xlsx"
              options(sheet_name='#byval1'                                     
                      suppress_bylines='on'                  
                      center_vertical='on'                                     
                      center_horizontal='on'                                   
                      orientation='landscape'                                  
                      blackandwhite='on'                                       
                      print_footer='Contact Peter at 555-5555 with
                                    questions'  
                      print_header="Report created on &currDate");            
    
    proc report ...
    run;
    
    ods excel close; 

    1. The %LET statement creates a macro variable named currDate that stores the current date.
    2. The %PUT statement prints the currDate macro variable in the log.
    3. The OPTIONS option specifies suboptions to modify the printing options in the Excel workbook.
    4. The CENTER_VERTICAL= suboption centers the worksheet vertically when printing.
    5. The CENTER_HORIZONTAL= suboption centers the worksheet when printing.
    6. The ORIENTATION= suboption orients the printed page as either portrait or landscape.
    7. The BLACKANDWHITE= suboption enables printing of the worksheet in black and white.
    8. The PRINT_FOOTER= suboption specifies the text that is placed in the footer when printing. If a footnote is specified, that footnote is used. Otherwise, this text is placed in the footer.
    9. The PRINT_HEADER= suboption specifies the text that is placed in the header when printing. If no title is specified, this text is used by Excel on the printed page. If a title has been specified with the TITLE statement, that title is used. This suboption uses the currDate macro variable to use the current date.

  2. Highlight the Step 4 code and click Run.

  3. View the log. Confirm that the xls02d04_Print_Layout.xlsx file was successfully created.

Step 5: Download and open the Excel workbook.

  1. Download and open the xls02d04_Print_Layout.xlsx file from the output folder.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Select File > Print. In the Settings section, select Print Entire Workbook from the drop-down list.

  3. On the right side of the screen, view the print preview. Notice the following:

    1. The table is aligned horizontally and vertically on the paper.
    2. The table is now shown in black and white.
    3. The paper has been changed to landscape orientation.
    4. The paper has a title with today's date and a footer with information about who to contact with any questions.

  4. Close the Excel file.

Working with SAS® and Microsoft Excel
Lesson 02, Section 3 Demo: Applying Styles to the Excel Report

This demonstration illustrates how to modify the styles of SAS procedures to create the Excel report.

Setup

Open the xls02d05.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Create a simple Excel report using the default style

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d05a_Styles.xlsx"                
              options(embedded_titles="on"          
                      embedded_footnotes="on");  
    
    * Worksheet 1 - Tabular  Report *;  
    title height=14pt "Actual vs Predicted Sales by Country and Year";
    footnote "Created on June 18th";
    proc print data=pg.country_sales noobs;
        sum Actual Predict;
    run;
    title;
    
    
    * Worksheet 2 - Visualization *;
    title height=14pt "Total Sales by Year";
    proc sgplot data=pg.country_sales;
        vbar Year /
             response=Actual;
    run;
    title;
    footnote;
    
    ods excel close; 

    1. The ODS EXCEL statement creates an Excel file named xls02d05a_Styles.xlsx and embeds titles in the Excel report.
    2. The TITLE and FOOTNOTE statements add titles and footnotes to the Excel report.
    3. The PRINT procedure prints the data in the c. The PRINT procedure prints the data in the pg.country_sales SAS table.SAS table.
    4. The SGPLOT procedure creates a visualization for the pg.country_sales SAS table.

  2. Highlight the Step 1 code and click Run.

  3. View the log. Confirm that the xls02d05a_Styles.xlsx file was successfully created.

Step 2: Download and open the Excel workbook.

  1. Download and open the xls02d05a_Styles.xlsx file from the output folder. Notice the following:

    1. The Excel workbook contains two worksheets: a single table and a visualization.
    2. The output table has a blue title, a light blue column header, and a footer with dark blue text.
    3. The second worksheet contains a visualization with a light fill color in the bar chart.
      Note: If you are using SAS Viya your default style might differ.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 3: View available style templates.

  1. Review the code in Step 3 of the program.

    Open code or syntax in a separate window.

    proc template;
        list styles;
    run; 

    1. The TEMPLATE procedure enables you to customize the look of your SAS output.
    2. The LIST statement lists SAS style templates that are available in your environment.

  2. Highlight the Step 3 code and click Run.

  3. View the results.

    1. The procedure returns a table with the available styles in your environment.
    2. Scroll down and confirm that Styles.Daisy exists.

Step 4: Create an Excel report using a specified style.

  1. Review the code in Step 4 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d05a_Styles.xlsx"                
              options(embedded_titles="on"          
                      embedded_footnotes="on")  
              style=Daisy;
    ...
    ods excel close; 

    1. The STYLE= option specifies one or more style overrides to use when writing output files.

  2. Highlight the Step 4 code and click Run.

  3. View the log. Confirm that the xls02d05a_Styles.xlsx file was successfully created.

Step 5: Download and open the Excel workbook.

  1. Download and open the xls02d05a_Styles.xlsx file from the output folder. Notice the following:

    1. The output table has a black, gray, and white style.
    2. The second worksheet contains a visualization with a dark blue fill color in the bar chart.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 6: Modify the style of a procedure.

  1. Review the code in Step 6 of the program.

    Open code or syntax in a separate window.

    * Set macro variables as the colors to use *;
    %let darkBlue=cx04304b;
    %let darkGray=cx768396;
    %let lightGray=lightgray;
    %let offWhite=whitesmoke;
    
    ods excel file="&outpath/xls02d05b_ProcStyle.xlsx"    
              options(embedded_titles='on'
                      sheet_interval='none');
    
    
    /* Default Style */
    title "Actual vs Predict Sales by Year and Country";
    proc print data=pg.country_sales label;
        id Country;
        var Year Actual Predict DiffFromPredict;
        sum Actual Predict DiffFromPredict;
    run;
    title;
    
    
    /* Modified Style */
    title height=14pt justify=left color=&darkBlue
          "Actual vs Predict Sales by Year and Country";
    proc print data=pg.country_sales 
               label
               style(header obs obsheader)=[color=&offWhite
                                            backgroundcolor=&darkBlue 
                                            fontsize=11pt]
               style(grandtotal)=[backgroundcolor=&lightgray];                                                    
        id Country / style(data)=[textalign=right];
        var Year Actual Predict DiffFromPredict /  
                     style(data)=[backgroundcolor=&offWhite 
                                   color=&darkBlue 	                                                            
                                   fontsize=9pt];
        sum Actual Predict DiffFromPredict /  
                     style(grandtotal)=[fontsize=12pt 	                                                                      
                                        fontweight=bold	                                                         
                                        color=&darkBlue];
    run;
    title;
    
    ods excel close; 


    1. The %LET statements create four macro variables with specific colors as values. The macro variables will be used to specify the colors in the PRINT procedure.
    2. The first PRINT procedure uses the default style template.
    3. The second PRINT procedure uses the STYLE= option to specify one or more style overrides to use for columns or rows.
    4. Within the STYLE= option, you specify the location to modify. The location identifies the part of the report that the STYLE= option affects. This code modifies the style of a variety of locations like the HEADER, OBS, OBSHEADER, and GRANDTOTAL locations.
    5. Within brackets, you specify the name of the style attribute to modify and the style attribute value. A variety of style attributes are modified here like background color, color, font size, and font weight.

  2. Highlight the Step 6 code and click Run.

  3. View the log. Confirm that the xls02d05b_ProcStyle.xlsx file was successfully created.

Step 7: Download and open the Excel workbook.

  1. Download and open the xls02d05b_ProcStyle.xlsx file from the output folder. Notice the following:

    1. The first PRINT procedure output uses the default style template.
    2. The second PRINT procedure output uses the modified styles specified in the code.
      Note: If you are using SAS Viya, your default style might differ.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 8: Add conditional formatting to highlight certain values.

  1. Review the code in Step 8 - a of the program.

    Open code or syntax in a separate window.

    %let modernRed=CXFFA0A0;
    %let modernGreen=CX99FFBB;
    
    * Create the Formats *;
    proc format;
        * Set color for negative and positive values *;
        value ColorFmt
              low - 0 = "&modernRed"
              0 <- high = "&modernGreen";
    		
        * Bold negative values *;
        value WeightFmt
              low - 0 = "bold";
    quit;

    1. The %LET statement creates the macro variables modernRed and modernGreen. The macro variable values use hexadecimal codes to specify the colors.
    2. The FORMAT procedure enables you to define your own formats for variables. Formats can include style attributes.
    3. The first format that the FORMAT procedure creates is named ColorFmt. It will apply red to all values 0 and less, and it will apply green to all values greater than 0.
    4. The second format that the FORMAT procedure creates is named WeightFmt. It applies bold to all values less than 0.

  2. Highlight the Step 8 - a code and click Run.

  3. View the log. Confirm that the program created the two formats successfully.

  4. Review the code in Step 8 - b of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d05c_Formatting.xlsx";
                      
    proc print data=pg.country_sales label;
         var Country Year Actual Predict;
         var DiffFromPredict / style(data)=[backgroundcolor=ColorFmt.           
    	                                   fontweight=WeightFmt.];               
         sum DiffFromPredict / 
                        style(grandtotal)=[backgroundcolor=ColorFmt.];  
    run;
    
    ods excel close; 

    1. The ODS EXCEL statement creates a workbook named xls02d05c_Formatting.xlsx.
    2. The PRINT procedure applies the formats created earlier. The LABEL option specifies to show the column labels. The first VAR statement selects the Country, Year, Actual, and Predict columns. The second VAR statement selects the DiffFromPredict column and applies the ColorFmt and WeightFmt formats to the column. The SUM statement sums the DiffFromPredict column and applies the background color format ColorFmt to the grand total.

  5. View the log. Confirm that the xls02d05c_Formatting.xlsx file was successfully created.

Step 9: Download and open the Excel workbook.

  1. Download and open the xls02d05c_Formatting.xlsx file from the output folder. Notice the following:

    1. The Difference from Predicted (DiffFromPredict) column has the modified style template. The negative values have been formatted with a red background and the text has been bolded. The positive values have a green background.
    2. The grand total for Difference from Predicted has also been formatted and contains a red background because the total is negative.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Working with SAS® and Microsoft Excel
Lesson 02, Section 3 Demo: Specifying Excel Formats in a SAS Program

This demonstration illustrates how to use SAS to add Excel formats within procedures for the report.

Setup

Open the xls02d06.sas program in the demos folder. Run the libname.sas program if necessary.

Step 1: Create a sample table.

  1. Review the code in Step 1 of the program.

    Open code or syntax in a separate window.

    data sampledates;
        SASDate = mdy(11,3,2020);
        DateFmt = SASDate;
        format DateFmt date9.;
    run;
    
    ods select variables;
    proc contents data=sampledates;
    run;
    
    proc print data=sampledates noobs;
    run; 

    1. The DATA step creates a table named sampledates in the Work library.
    2. The SASDate column is a SAS date value that does not have a format.
    3. The DateFmt column is the same SAS date value with the DATE9 format.
    4. The CONTENTS procedure views the metadata of the new SAS table.
    5. The PRINT procedure prints the SAS table.

  2. Highlight the Step 1 code and click Run.

  3. View the log.Confirm that the program ran successfully.

  4. View the results. The results of the CONTENTS procedure show that the SASDate column has no format and thus displays the SAS date value 22222. The DateFmt column has the same exact date and the DATE9 format applied.

Step 2: Specify Excel date formats in a SAS procedure.

  1. Review the code in Step 2 of the program.

    Open code or syntax in a separate window.

    ods excel file="&outpath/xls02d06_ExcelFormats.xlsx"
              options(sheet_interval='none' 
                      embedded_titles='on'
                      absolute_column_width='10,10,20,10,10');
    
    
    * Specifying Excel formats correctly*;
    title "Specifying an Excel format on a column with a SAS date 
           format";
    proc print data=sampledates noobs;
        var DateFmt;                 
        var DateFmt / style(data) = {tagattr='format:ddmmyyyy'};
        var DateFmt / style(data) = {tagattr='format:dddd-mm-yyyy'};
        var DateFmt / style(data) = {tagattr='format:yyyy'};
        var DateFmt / style(data) = {tagattr='format:mmmm'};
        var DateFmt / style(data) = {tagattr='format:dd'};
    run;
    
    * Specifying Excel formats incorrectly *;
    title "Specifying an Excel format on a column without a SAS date 
           format";
    proc print data=sampledates noobs;
        var SASDate;
        var SASDate / style(data) = {tagattr='format:ddmmyyyy'};
        var SASDate / style(data) = {tagattr='format:dddd-mm-yyyy'};
        var SASDate / style(data) = {tagattr='format:yyyy'};
        var SASDate / style(data) = {tagattr='format:mmmm'};
        var SASDate / style(data) = {tagattr='format:dd'};
    run;
    
    ods excel close;

    1. The ODS EXCEL statement creates an Excel file named xls02d06_ExcelFormats.xlsx.
    2. The first PRINT procedure uses the DateFmt column to add explicit Excel formats using the TAGATTR= option. The DateFmt column has a SAS date value associated with the column.
    3. The second PRINT procedure applies the same Excel formats to the SAS date value column SASDate. The SASDate column is the same date value as the previously PRINT procedure, but it does not contain a SAS format.
    4. A SAS date format is needed in order for a SAS date value to be converted to an Excel date value prior to the specifying the Excel format.
      Note:
      In the comments below Step 2, there are notes about a few common Excel date formats and how to specify them to get you started. The first column shows what you want to display, and the second column shows the syntax.

  2. Highlight the Step 2 code and click Run.

  3. View the log. Confirm that the xls02d06_ExcelFormats.xlsx file was created successfully.

  4. View the results.

Step 3: Download and open the Excel workbook.

  1. Download and open the xls02d06_ExcelFormats.xlsx file from the output folder. Notice the following:

    1. The first table uses the DateFmt column. The DateFmt column contains a SAS date format. The first column is the default mapping of the DATE9 SAS format to Excel. The remaining four columns are Excel-specified formats using the TAGATTRS= option. Notice that the value displays the correct date of Tuesday, November 11, 2020.
    2. The second table uses the SASDate column, which is a SAS date value (22222) but does not contain a SAS format. The first column is the SAS date value 22222 (Tuesday, November 11, 2020). The remaining columns use the TAGATTRS= option to apply Excel formats on the SAS date value. Excel returns the date Wednesday, November 2, 1960. These are incorrect results. When you apply Excel formats using the TAGATTRS= option on a SAS date value that does not have a date format, incorrect results are returned in Excel.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 4: Specify other Excel formats.

  1. Review the code in Step 4 - a of the program.

    Open code or syntax in a separate window.

    * Create a sample table *;
    data samplenumbers;
        Default1 = .1234;
        'xlFmt: #.00'n = Default1;
        'xlFmt: 0.0#%'n = Default1;
        'xlFmt: 0.00\%'n = Default1;
        Default2 = 12345.678;
        'xlFmt: 0.###'n = Default2;   
        'xlFmt: #.0'n = Default2;
        'xlFmt: $#,###.00'n = Default2;    
    run;
    proc print data=samplenumbers;
    run; 

    1. The DATA step creates a sample table named samplenumbers. The columns are named after the specific Excel format that will be used later.
    2. The PRINT procedure previews the samplenumbers table.

  2. Highlight the Step 4 – a code and click Run.

  3. View the log. Confirm that the program ran successfully.

  4. View the results.

    1. The PRINT procedure returns an output table with eight columns.
    2. The first four columns have the value 0.1234.
    3. The remaining four columns have the value 12345.68.

  5. Review the code in Step 4 - b of the program.

    Open code or syntax in a separate window.

    * Create an Excel report with Excel formats *;
    ods excel file="&outpath/xls02d06_ExcelFormats.xlsx";
              
    proc print data=samplenumbers split=" " noobs;
        var Default1;
        var 'xlFmt: #.00'n  /  style(data) = {tagattr='format:#.00'};   
        var 'xlFmt: 0.0#%'n /  style(data) = {tagattr='format:0.0#%'};  
        var 'xlFmt: 0.00\%'n /  style(data) = {tagattr='format:0.00\%'};  
        var Default2;
        var 'xlFmt: 0.###'n /  style(data) = {tagattr='format:0.###'};  
        var 'xlFmt: #.0'n /  style(data) = {tagattr='format:#.0'};  
    	  var 'xlFmt: $#,###.00'n /  style(data) =   
                                         {tagattr='format:$#,###.00'};  
    run;
    
    ods excel close; 

    1. The PRINT procedure prints the samplenumbers table.
    2. The VAR statements select the column to print.
    3. The STYLE= option in the VAR statement overrides the default style. The DATA location specifies to modify the data. The TAGATTR= option with the keyword format applies the specified Excel format to the column when creating the report in Excel instead of relying on the default SAS to Excel format mapping.

  6. Highlight the Step 4 – b code and click Run.

  7. View the log. Confirm that the program ran successfully.

  8. View the results.

    1. The PRINT procedure returns the output table with eight columns as seen earlier. The Excel formats specified in the STYLE= option are not applied in the SAS results.
    2. The first four columns have the value 0.1234.
    3. The remaining four columns have the value 12345.68.

Step 5: Download and open the Excel workbook.

  1. Download and open the xls02d06_ExcelFormats.xlsx file from the output folder. Notice the following:

    1. The first column, Default1, shows the default value .1234 using the default SAS to Excel mapping., shows the default value .1234 using the default SAS to Excel mapping.
    2. The second column uses the Default1 value of .1234 and applies the Excel #.00 format and returns the value .12. The 0 placeholder displays insignificant zeros if a number has fewer digits than the format. The # placeholder does not display zeros when the number has fewer digits than the format. Because the number has more digits to the right of the decimal point than there are placeholders, the number is rounded. value of .1234 and applies the Excel #.00 format and returns the value .12. The 0 placeholder displays insignificant zeros if a number has fewer digits than the format. The # placeholder does not display zeros when the number has fewer digits than the format. Because the number has more digits to the right of the decimal point than there are placeholders, the number is rounded.
    3. The third column uses the Default1 value of .1234 and applies the Excel 0.0#% format and returns the value 12.34%. The 0 placeholder displays insignificant zeros if a number has fewer digits than the format. The # placeholder does not display zeros when the number has fewer digits than the format. Including the percent sign with no leading backslash in a format multiplies the number by 100 and displays the percent sign.
    4. The fourth column uses the Default1 value of .1234 and applies the Excel 0.00\% format and returns the value 0.12%. Including a percent sign with a leading backslash in a format displays the percent sign and does not multiply the number by 100.
    5. The fifth column Default2 shows the default value 12345.68 using the default SAS to Excel mapping. Select the value and look at the cell above. Notice that the real value is 12345.678 and, by default, the value is rounded.
    6. The sixth column uses the Default2 value of 12345.678 (shown in Excel as 12345.68) and applies the Excel 0.### format and returns the full value 12345.678. The # placeholder adds three placeholders and does not display zeros when the number has fewer digits than the format.
    7. The seventh column uses the Default2 value of 12345.678 (shown in Excel as 12345.68) and applies the Excel #.0 format and returns the full value 12345.7. The 0 placeholder rounds the number format to the nearest tenth.
    8. The last column uses the Default2 value of 12345.678 (shown in Excel as 12345.68) and applies the Excel $#,###.00 format and returns the value $12,345.68. The 0 placeholders round the number format to the nearest hundredth. The # to the left of the decimal formats the value using commas and adds a dollar sign.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.

Step 6: Add Excel formulas.

  1. Review the code in Step 6 - a of the program.

    Open code or syntax in a separate window.

    * Create a SAS table *;
    data work.country_sales;
         set pg.country_sales;
         CalcExcelColumn = .;          
         drop ForecastAccuracy
              DiffFromPredict;
    run; 

    1. The DATA step creates a sample table named country_sales using the pg.country_sales table. The new table creates a dummy column named CalcExcelColumn that will be calculated by specifying an Excel function in SAS.

  2. Highlight the Step 6 – a code and click Run.

  3. View the log. Confirm that the program ran successfully.

  4. View the output data in the OUTPUT DATA tab. Notice the CalcExcelColumn has a missing value for each row.

  5. Review the code in Step 6 - b of the program.

    Open code or syntax in a separate window.

    * Add an Excel formula *;
    ods excel file="&outpath/xls02d06_ExcelFormats.xlsx";
    
    proc print data=work.country_sales;
         var Country Year Actual Predict;
         var CalcExcelColumn / 
                    style(data)={tagattr='formula:=RC[-2] - RC[-1]'};
    run;
    
    ods excel close;

    1. The STYLE= option in the VAR statement enables the use of Excel formulas.
    2. To add an Excel formula, specify the TAGATTRS= option. As the value, add the keyword FORMULA with the Excel formula that you would like to use. The RC value corresponds to the cell relative to the current cell. For example, RC[-1] means 1 cell to the left of the current cell. This formula will take the column Actual and subtract Predict for each row.
    3. Any valid Excel formula can be used here.

  6. Highlight the Step 6 – b code and click Run.

  7. View the log. Confirm that the program ran successfully.

  8. View the results. In the results, the column CalcExcelColumn has a missing value because the Excel formulas are not executed in SAS.

Step 7: Download and open the Excel workbook.

  1. Download and open the xls02d06_ExcelFormats.xlsx file from the output folder. Notice the following:

    1. The column CalcExcelColumn has a value. It was calculated by taking the relative cell (RC) reference -2(Actual) minus -1(Predict).
    2. Select column F2. Notice that the formula that was specified is now in Excel.

    Note: For comparison, you can download and open the course copy of the Excel file.

  2. Close the Excel file.