Free SAS Training - Enterprise Guide - Filter and Sort

The SAS Training video clip demo below is a simple example of how to open a project and then use the interactive query building facility of SAS Enterprise Guide to build a query to filter and sort the data. The output file is a SAS data set. The goal is to create a file filtered so that only USA sales are shown and the records are sorted by state.

The video shows how to open an existing project (the one that we created in the previous steps) and modify it so that a sorted, filtered SAS data set is created. Start this task by opening the project and then right clicking the short-cut to the furniture sales spreadsheet and choosing 'Filter and Query'. For this example we are going to choose Country, State, Actual Sales, Product and Product-Type as columns to include in our query. Use the arrows on the right to re-order how the columns will appear on output and place the actual sales at the end.

Next, try out the filtering capabilities by clicking the 'Filter Data' tab, adding a new filter and choosing Country=USA as the filter. Finally, let's sort the output by clicking the 'Sort Data' tab and choosing State as our variable to sort on. As in the other lessons, click 'Run' and view the output file. Note how the data is sorted.

If you are familiar with SAS coding and Proc SQL we can take a peek at the SAS code that was  generated in this step. To see the code, open up the query node that we just created and click on 'Preview'. There you see the Proc SQL code that was generated. It looks as follows:

PROC SQL;
 CREATE TABLE SASUSER.QUERY_FOR_FURNITURESALES_XL_0000 AS SELECT FurnitureSales_xls__FurnitureSa.COUNTRY,
	 FurnitureSales_xls__FurnitureSa.STATE,
	 FurnitureSales_xls__FurnitureSa.PRODTYPE,
	 FurnitureSales_xls__FurnitureSa.PRODUCT,
	 FurnitureSales_xls__FurnitureSa.ACTUAL_SALES 
 FROM WORK.FurnitureSalesDetails_4629 AS FurnitureSales_xls__FurnitureSa
 WHERE FurnitureSales_xls__FurnitureSa.COUNTRY = "U.S.A."
 ORDER BY FurnitureSales_xls__FurnitureSa.STATE;
QUIT;

After the data file is generated, you can now save all of the steps above as an EG project that can be modified at a later time. The output data set remains part of the project.