Setting Up Your Practice Files (REQUIRED)

To complete the activities, demos, and practices in this course, you must access SAS software and set up your practice files.

Using the SAS Virtual Lab

We recommend using the SAS Virtual Lab to take this course because most of the setup is done for you. To get started, you simply launch the lab and run one program. Open and follow the instructions here:

Setup instructions for the SAS Virtual Lab

Using Other SAS Environments

If you prefer to use your own SAS Environment or SAS OnDemand for Academics to take this course, open and follow the instructions here:

Setup instructions for other SAS environments


Lesson 01

Working with SAS® and Microsoft Excel
Lesson 01, Section 1 Activity: SAS/ACCESS Interface to PC Files Documentation

View the SAS/ACCESS Interface to PC Files documentation.

  1. In a browser, go to SAS Documentation and click SAS Programming: SAS Viya or SAS Programming: SAS 9.4 and Viya 3.5, depending on your need.

  2. In the left navigation pane, select Data Access and then select the section that begins with SAS/ACCESS® 9.4 Interface to PC Files or SAS/ACCESS for PC Files for Viya.

  3. In the right pane select Working with SAS/ACCESS Interface to PC Files > Methods for Accessing PC Files Data.

  4. In the documentation, identify the file types that you can read from a SAS data set and that you can write to a SAS data set. Are there more than five file types?


Working with SAS® and Microsoft Excel
Lesson 01, Section 2 Activity: Importing an Excel Worksheet into SAS

Open xls01a02.sas from the activities folder and perform the following tasks to import a Microsoft Excel worksheet into SAS:

  1. Navigate to the course data folder. Download the cars_origin.xlsx Excel workbook and then open the file. Confirm that the workbook contains three worksheets: Asia, USA, and Europe. Close the Excel workbook.

  2. Complete the IMPORT procedure to import the Europe worksheet into SAS. Use the path macro variable to specify the course folder location and use the XLSX engine as the data source identifier. Name the SAS table Europe and place it in the Work library.
    proc import datafile="&path/data/cars_origin.xlsx"... 
  3. Run the IMPORT and CONTENTS procedures. View the results. How many rows are in the new SAS table?


Working with SAS® and Microsoft Excel
Lesson 01, Section 2 Activity: Exporting a SAS Table to Microsoft Excel

Open xls01a03.sas from the activities folder and perform the following tasks to export a SAS table to Microsoft Excel:

  1. Highlight and run the CONTENTS and PRINT procedures to preview the SAS table. Notice that many of the columns contain labels and formats.

  2. Complete the EXPORT procedure to export the SAS table pg.cars to Excel using the XLSX data source identifier. Create an Excel workbook named xls01a03.xlsx and use the outpath macro variable to specify the file location. Name the worksheet Raw Data and export the data using column labels as headings.
    proc export ... outfile="&outpath/xls01a03.xlsx" 
  3. Run the EXPORT procedure. Confirm that the procedure executed successfully.

  4. Download and open the xls01a03.xlsx Excel workbook from the output folder. What is the name of the heading in Excel column H?


Working with SAS® and Microsoft Excel
Lesson 01, Section 3 Activity: Connecting to Excel Using the LIBNAME Statement

Open xls01a04.sas from the activities folder and perform the following tasks to make a library reference to an Excel workbook:

  1. Navigate to the course data folder. Download and open the cars_origin.xlsx Excel workbook. Confirm that the workbook contains three worksheets: Asia, USA, and Europe.

  2. Add the following LIBNAME statement to connect to the cars_origin.xlsx Excel file.
    libname pgxl xlsx "&path/data/cars_origin.xlsx";  
  3. Run the LIBNAME statement. Then, in the navigation pane, select Libraries and expand the pgxl library. Notice that the pgxl library contains a reference to each worksheet.

  4. Run the FREQ procedure and the LIBNAME statement that contains pgxl clear. What is the frequency value of All?


Lesson 02

Working with SAS® and Microsoft Excel
Lesson 02, Section 1 Activity: ODS Documentation - ODS EXCEL Statement

Use the SAS documentation for the SAS Output Delivery System (ODS) to find information about ODS EXCEL options.

  1. In a browser, go to SAS Documentation and click SAS Programming: SAS Viya or SAS Programming: SAS 9.4 and Viya 3.5, depending on your need.

  2. In the left navigation pane, select Output and Graphics > Output Delivery System (ODS) > SAS Output Delivery System: User's Guide.

  3. Select ODS Statements > Dictionary of ODS Language Statements > ODS EXCEL Statement.

  4. In the right pane, in the Summary of Optional Arguments section, find the FILE= option. What does the FILE= option do?


Working with SAS® and Microsoft Excel
Lesson 02, Section 1 Activity: Creating a Simple Excel Report

Open xls02a01.sas from the activities folder and perform the following tasks to create an Excel report using the ODS EXCEL statement:

  1. Execute the SGPLOT and PRINT procedures to view all rows of the cars table from the pg library and visualize the data. Confirm that the program executed successfully.

  2. Add the following ODS EXCEL statement before the SGPLOT procedure:
    ods excel file="&outpath/xls02a01.xlsx"; 
  3. Below the PRINT procedure, add an ODS statement to close the EXCEL destination.

  4. Execute the ODS statements and procedures.

  5. Download and open the xls02a01.xlsx Excel workbook from the output folder. How many worksheets are in the Excel workbook?


Working with SAS® and Microsoft Excel
Lesson 02, Section 2 Activity: ODS Documentation - ODS EXCEL Suboptions

Use the SAS documentation for the SAS Output Delivery System (ODS) to find information about ODS EXCEL suboptions.

  1. In a browser, go to SAS Documentation and click SAS Programming: SAS Viya or SAS Programming: SAS 9.4 and Viya 3.5, depending on your need.

  2. In the left navigation pane, select Output and Graphics > Output Delivery System (ODS) > SAS Output Delivery System: User's Guide.

  3. Select ODS Statements > Dictionary of ODS Language Statements > ODS EXCEL Statement.

  4. In the right pane, scroll down and select OPTIONS(<suboptions>).

  5. Find the SHEET_INTERVAL suboption. What does the NONE value do?


Working with SAS® and Microsoft Excel
Lesson 02, Section 2 Activity: Modifying Output to Excel Worksheets

Open xls02a02.sas from the activities folder and perform the following tasks to create worksheets using BY groups:

  1. Review the program. The SORT procedure sorts pg.cars, and the PRINT procedure with the BY statement creates an output table for each Origin value. Run the program.

  2. In the output folder, download and open xls02a02.xlsx. Notice that the PRINT procedure with the BY statement creates three worksheets with default names, and the top of each worksheet lists the BY group.

  3. Add the following suboptions in the ODS statement and run the program:
    options(sheet_interval='bygroups' suppress_bylines='on' 
    		  sheet_label='Origin')
  4. Download and open xls02a02.xlsx. What is the name of the first worksheet? Are the BY group names listed at the top of the worksheet?


Working with SAS® and Microsoft Excel
Lesson 02, Section 2 Activity: Modifying Table and Worksheet Appearance

Open xls02a03.sas from the activities folder and perform the following to modify the appearance of a worksheet and table:

  1. Run the program and then download and open xls02a03.xlsx from the output folder. Confirm that the Excel workbook has no title, autofilter, or frozen headers.

  2. In the ODS statement, add suboptions to do the following:
    • Embed the SAS title.
    • Turn on the autofilter.
    • Freeze headers so that column names always show (headers at row 3).
    • Freeze row headers so that the Make and Model column always show
      (row headers at row 2).

  3. Run the program to update xls02a03.xlsx. What four suboptions did you use to correctly create the Excel workbook?


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

Open xls02a04.sas from the activities folder and perform the following tasks to apply styles to an Excel report:

  1. Execute the program to use the PRINT and SGPLOT procedures to create an Excel report in the output folder named xls02a04.xlsx.

  2. Download and open the xls02a04.xlsx Excel workbook from the output folder. Notice that the illuminate style template is not optimized to work with ODS Excel.

  3. Add the analysis style template in the ODS EXCEL statement and execute the program to overwrite the xls02a04.xlsx Excel workbook.

  4. Download and open the xls02a04.xlsx Excel workbook. View both worksheets. Did the analysis style template create a usable Excel report?


Working with SAS® and Microsoft Excel
Lesson 02, Section 3 Practice: Creating an Excel Report

In the starter program for this practice, the DATA step and SAS procedures are already complete. Your goal is to use the SAS results to create a specific Microsoft Excel workbook.

Reminder: If you restarted your SAS session, submit your libname.sas program from your course folder to set up the course macro variables and library reference.

Step 1: Prepare the Excel data and create a SAS table

Open the starter program and modify the code in the section named STEP 1: Prepare the Excel data and create a SAS table.

  1. Open xls02p01.sas from the practices folder. View the code.

    Solution:

    The program contains a DATA step and a SORT procedure step to prepare the data, and a variety of SAS procedures to create report output.

  2. Above the DATA step, do the following:
    1. Specify the VALIDVARNAME= option with the value V7.
    2. Create a library reference named carsxl to the cars.xlsx Excel workbook in the data folder. Specify the following location: &path./data/cars.xlsx.

    Solution:

    options validvarname=v7;
    libname carsxl xlsx "&path./data/cars.xlsx";  
    * Prepare the data *;
    data work.cars_final;
           set carsxl.cars;
           MPG_Avg = mean(MPG_City, MPG_Highway);
           format MSRP Invoice dollar16.;
           label MSRP = "Manufacturer's Suggested Retail Price"
                   MPG_City = 'MPG City'
                   MPG_Highway = 'MPG Highway'
                   MPG_Avg = 'MPG Average'
                   EngineSize = 'Engine Size (L)'
                   Weight = 'Weight (LBS)'
                   Wheelbase = 'Wheelbase (IN)'
                   Length = 'Length (IN)';
    run;
    
    * Sort the data *;
    proc sort data=work.cars_final;
            by Origin Make Type Invoice;
    run; 

  3. After the PROC SORT step, clear the carsxl libref.

    Solution:

    ...
    * Sort the data *; proc sort data=work.cars_final; by Origin Make Type Invoice; run; libname carsxl clear;

  4. Run the entire program and view the results of the procedures. Confirm that no errors occurred.

Step 2: Create the Excel report

In the section of the program named STEP 2: Create the Excel Report, add options and suboptions that create an Excel report.

  1. Add the ODS EXCEL statement below the %LET statement and specify the following:
    1. Create an Excel file named Final_Cars_Report_ <current date> in the output folder using the currentDate macro variable. Specify the following location: &outpath./data/<file-name>.
    2. Turn on the suboptions to embed titles and footnotes of tabular output in the worksheet.
    3. Specify the NONE suboption for sheet intervals.

    Solution:

    ods noproctitle;
    %let currentDate = %sysfunc(today(),yymmdd10.);
    
    ods excel file="&outpath./Final_Cars_Report_&currentDate..xlsx"  
         options(embedded_titles="on"          
                 embedded_footnotes="on"  
                 sheet_interval='none');  

  2. Under the comment Worksheet 1, add the ODS EXCEL statement and specify the following for the worksheet:
    1. Name the worksheet with the results of the PRINT procedure output (Detailed Data).
    2. Turn on the table autofilter suboption.
    3. Freeze the headers at row 4 in the worksheet.
    4. Freeze the row headers at row 2 in the worksheet.

    Solution:

    ods excel options(sheet_name='Detailed Data'
                     autofilter='all'                                          
                     frozen_headers='4'                
                     frozen_rowheaders='2');          
    
    title height=18pt 'Detailed Car Data';
    
    title2 "Data as of &currentDate";
    proc print data=work.cars_final label noobs;
            id Make Model;
    run;
    title; 

  3. Under the comment Worksheet 2, add the ODS EXCEL statement and specify the following:
    1. Create a new worksheet with the results of the SGPLOT and MEANS procedures and name the worksheet MPG Analysis.
    2. Turn off the frozen headers and row headers for the worksheet.

    Solution:

    ods excel options(sheet_name='MPG Analysis'
                     sheet_interval='now'                                           
                     frozen_headers='off'                 
                     frozen_rowheaders='off');  
    
    ods graphics / width=8in;
    title height=14pt justify=left 'Miles Per Gallon by Car Make';
    proc sgplot data=work.cars_final;
            vbar Make /
                   response=MPG_Avg
                   stat=mean
                   categoryorder=respdesc
                   nooutline
                   fillattrs=(color=dodgerBlue);
            yaxis label='MPG Average';
    quit;
    ods graphics / reset;
    title;
    
    proc means data=work.cars_final min mean max maxdec=0;
           class Make;
           var MPG_Avg;
    quit; 

  4. Under the comment Worksheet 3, add the ODS EXCEL statement and specify the following:
    1. Create a new worksheet for each distinct BY group of the PRINT procedure. The PRINT procedure uses the BY statement to group the rows by Origin.
    2. Name each worksheet using the BY group values (Hint: Use #byval1 for the sheet name).
    3. Freeze the headers at row 4.

    Solution:

    ods excel options(sheet_name='#byval1'  
                     sheet_interval='bygroup' 
                     frozen_headers='4');
    * Create the format *;
    proc format;
            value under30kMSRP
                   0 - 30000 = "lightblue";
    quit; 

  5. In the PRINT procedure, add the STYLE= option to the VAR statement for the MSRP column. Specify the under30kMSRP custom SAS format from the FORMAT procedure above to color the background of all MSRP values that are under $30,000.

    Solution:

    title height=10pt 'Highlighted cars are under $30,000';
    proc print data=work.cars_final noobs;
            by Origin;
            var Make Model Type;
            var MSRP / style(data)=[backgroundcolor=under30kMSRP.]; 
    run; 

  6. At the end of the program, close the ODS EXCEL destination.

    Solution:

    ods excel close;

  7. Run the program to create the Excel report. Open the Excel workbook from the output folder. What is the name of the workbook?

    Solution:

    What is the name of the Excel workbook? Final_Cars_Report_<current date>.xlsx

    If the file name does not contain the current date, refer to the solution for practice step 3.

    Note: In the practices folder, you can find the final solution program (named xls02p01_solution.sas) and a copy of the final Excel workbook (named Final_Cars_Report_Practice_Solution.xlsx).

  8. Answer the following questions:
    1. How many worksheets are in the Excel workbook?
    2. What are the names of the worksheets?
    3. Select the Detailed Data worksheet and scroll to the bottom. Can you still see the table column headers?
    4. Select the Europe worksheet. Does the MSRP value of the first row in the table have a background color?

    Solution:

    1. How many worksheets are in the Excel workbook? Five worksheets
    2. What are the names of the worksheets? Detailed Data, MPG Analysis, Asia, Europe, USA
    3. Select the Detailed Data worksheet and scroll to the bottom. Can you still see the table column headers? Yes, the header is frozen at row 4 and the column names are always shown.
    4. Select the Europe worksheet. Does the MSRP value of the first row in the table have a background color? Yes, the MSRP value $25,940 has a background color because it is less than $30,000.