SAS Data Integration Studio P34:This manual is a companion to the online Help for SAS Data Integration Studio. The Help describes all windows in SAS Data Integration Studio, and it summarizes the main tasks that you can perform with the software. The Help includes examples for all source designer wizards, all target designer wizards, and all transformation templates in the Process Library tree. | 160 Configure the SQL Join Transformation A Chapter 10 Specify GROUP BY Options for the Join In the previous section the Total_Retail_Price column in the join result table was changed to a calculated column that sums all of its values. All retail transactions for all employees would be added together. However the report that you want to create summarizes the retail transactions for each employee. To limit the scope of the Total_Retail_Price column to each employee add a GROUP BY option that applies to all of the other mapped columns in the join. That is you would specify the following columns in the Group By tab of the SQL Join transformation Employee_Name Employee_ID Job_Title Company Department Section and Org_Group. Follow these steps to specify GROUP BY options in the SQL Join transformation 1 In the properties window for the SQL Join transformation click the Group By tab. 2 In the Columns pane select all columns except the Total_Retail_Price column. Then click the right arrow to move the columns into the Group by columns pane. The Group By tab should resemble the following display. Display Columns Specified on the Group By Tab 3 Click Apply to save your changes without closing the properties window. 4 Optional To see how the SQL code is changed by the expression that you just defined click the SQL tab. The code on the SQL tab should resemble the following sample SELECT ORGANIZATION_DIM n. Employee_ID n ORGANIZATION_DIM n. Company n ORGANIZATION_DIM n. Department n ORGANIZATION_DIM n. Section n ORGANIZATION_DIM n. Org_Group n ORGANIZATION_DIM n. Job_Title n ORGANIZATION_DIM n. Employee_Name n SUM ORDER_FACT n. Total_Retail_Price n format 8. AS Total_Retail_Price n FROM orstar n. ORGANIZATION_DIM n INNER JOIN orstar n. ORDER_FACT n ON ORDER_FACT n. Employee_ID n ORGANIZATION_DIM n. Employee_ID n Example Process Flows A Configure the Loader Transformation 161 GROUP BY ORGANIZATION_DIM n. Employee_ID n ORGANIZATION_DIM n. Company n ORGANIZATION_DIM n. .
