JULIA SAS Enterprise Guide Manual

JULIA
SAS Enterprise Guide Manual
Compiled by JULIA Team,
Business Reporting and Intelligence and Data Governance (BRIDG)
Email: [email protected]
Website: https://www.julia.unsw.edu.au/SASPortal
Last Updated: 23 April 2014
SAS Enterprise Guide Support ................................................................................... 2
How to Start SAS Enterprise Guide ........................................................................... 3
Adding Table(s) to SAS Enterprise Guide Project Workspace ................................... 8
Query Builder - Join and Filter Tables ...................................................................... 14
Filter and Sort the Data ............................................................................................ 23
Import External File into Project Workspace ............................................................ 25
Export Table Content into Excel ............................................................................... 28
Save Project to Local Drive ...................................................................................... 29
Appendix - SAS Enterprise Guide Tips and Tricks ................................................... 30
[email protected]
1
SAS Enterprise Guide Support
If you need help with:




Access to SAS Enterprise Guide
SAS Enterprise Guide Installation
Using SAS Enterprise Guide
Technical help
Please send an email to [email protected] and one of our friendly team
members will be happy to help.
This document can be found at:
http://julia.unsw.wikispaces.net/file/view/JULIA+SAS+Enterprise+Guide+Manual.pdf
[email protected]
2
How to Start SAS Enterprise Guide
Step 1: Open SAS Enterprise Guide Application
On windows desktop, click Start -> All programs -> SAS -> SAS Enterprise Guide
(Note: Select the version that is installed on your machine)
[email protected]
3
Step 2: Setup SAS server
If the profile hasn’t been setup, there will be a pop-up saying that no SAS server is
available and ask the user to modify the profile information. (Go to step 3 if the
profile has been setup).
To setup a profile, please click the ‘Yes’ button in the pop-up or click ‘no profile
selected’ link at the bottom-right of the screen to have the profile screen displays.
[email protected]
4
On the connection popup window, click the ‘Add’ button.
Put the SAS Production Server in the ‘Name’ field and
dwspsaa002.prod.it.unsw.edu.au in the ‘Machine’ field. Please make sure the port
is set to ‘8561’ and the ‘Remote’ radio button is selected. The user also needs to put
the staff Zid and Zpass in the ‘User’ and ‘Password’ fields respectively.
For the future convenience, it is recommended to save login in the profile by ticking
the ‘Save login in profile’. After finished with the changes, please click ‘Save’
button.
[email protected]
5
Choose the profile that has been created and click ‘Set Active’ button followed by
‘Close’ button.
.
Step 3: Check if the SAS enterprise guide has been connected to the server
If the SAS enterprise guide has been connected to the server, the user will be able to
see the user’s name followed by the server name at the bottom-right of the screen.
[email protected]
6
Step 4: Create SAS enterprise guide project
It is necessary to create a new project before the user is able to do some work in the
SAS enterprise guide.
To create a project, click File -> New -> Project.
[email protected]
7
Adding Table(s) to SAS Enterprise Guide Project Workspace
The SAS tables are sitting under the SAS library in the server. To be able to add the
table, the user needs to locate the server and library.
Step 1: Locate the server
Click the ‘Server List’ icon in the SAS enterprise guide side bar.
[email protected]
8
Step 2: Locate the library
Expand the ‘Servers’ by clicking the + button on the left. And then expand the
‘SASApp’ followed by expanding the ‘Libraries’.
[email protected]
9
Step 3: Locate the table
The tables are sitting under the library and each library has different name that
represent the different category. For example, to get the student related dimension
table, the user needs to expand the BI_STUDENT_T1_DIM library.
Please note that, each user will have a different access. In that case, one user may
see the BI_STUDENT_T1_DIM while the other user may not.
[email protected]
10
Step 4: Add table(s) to the project workspace area
To add the table(s) to the project, the user needs to drag the table from the side bar
and drop it to the project area OR double click on the table.
[email protected]
11
By default, the system will open the table automatically. The user can go back to the
project workspace area by closing the table or double click the ‘Process Flow’ icon
in the side bar.
This feature can be disabled by go to Tools -> Options -> Data -> Data General
and un-tick ‘Automatically open data when added to project’.
[email protected]
12
Step 5: Open the table content
The user can explore the data inside the table by right clicking on the table and
select ‘Open’.
[email protected]
13
Query Builder - Join and Filter Tables
Step 1: Add the necessary tables to the project workspace area
All the necessary tables need to be added the project workspace area before doing
any join.
Step 2: Add query builder task into the project area
There are 2 different ways to add the query builder task into the project area:
1. Right click one of the tables that will be joined and select ‘Query Builder’
[email protected]
14
2. Add query builder task by clicking Tasks-> Data-> Query Builder
A pop-up will open after selecting Query Builder.
[email protected]
15
Step 3: Join the tables.
Define the join condition by clicking ‘Join Table’ in the query builder window.
Click ‘Add Tables’ in the ‘Tables and Joins’ window to add more tables to the
query.
[email protected]
16
Select the table(s) and click ‘Open’.
Add new join condition by dragging and dropping the column from one table to
another.
[email protected]
17
Define the join properties and click ‘OK’.
Click the ‘Close’ button to close the ‘Tables and Joins’ window.
Please note: If the join key is not found in the new added table, the system will
automatically create the join based on the column with similar name. In that case,
the user needs to manually remove the join and re-define a new join.
[email protected]
18
Step 4: Define the output column(s) and apply filter
Select the output column(s) by drag and drop the column(s) from the column list to
the select data area.
The user can filter or sort the query by clicking the ‘Filter Data’ or Sort Data’ tab in
the query builder window.
[email protected]
19
Drag and drop the column to be filtered into the filter data area.
Define the filter and click ‘Finish’.
[email protected]
20
The user can aggregate the output column(s) by clicking the ‘Summary’ column in
the select data area. (1)
The user will also be able to distinct the output by ticking ‘Select distinct row only’.
(2)
It is recommended to rename the query builder process and output table to make it
easier to identify. Please note that the output table name should follow the following
format ‘WORK.<<Output_Table_Name>>’. (3)
[email protected]
21
Click ‘Run’ to run the query.
[email protected]
22
Filter and Sort the Data
Right click on the table and select ‘Filter and Sort’.
Select the column(s) for the output table.
[email protected]
23
Define the filter by clicking the ‘Filter’ tab and set the filter condition.
Sort the data by clicking the ‘Sort’ tab and define the columns.
[email protected]
24
Import External File into Project Workspace
Import the file by clicking File -> Import Data.
Choose the external file and click ‘Open’.
[email protected]
25
Make sure the library has been set to ‘WORK’ and click ‘Next’ to continue.
Set the data source setting and click ‘Next’. This setting can be different for each file
type.
[email protected]
26
Choose the column(s) to be included in the upload and specify the data format and
size.
Click ‘Finish’.
[email protected]
27
Export Table Content into Excel
Open the table by right click on the table and select ‘Open’.
Click ‘Send To’ at the top of the table and select ‘Microsoft Excel’.
[email protected]
28
Save Project to Local Drive
Click File -> Save Project.
Choose the file location and click ‘Save’.
[email protected]
29
Appendix - SAS Enterprise Guide Tips and Tricks
1. When user is joining tables, there is a box under the diagrams showing what
columns the user is joining between tables. If that dialog box disappears, click on
Join Order at the top of the screen.
2. User can rename task and output table for reference and it will be easier to
identify which table to join with.
3. If user users Filter (no calculated fields required), the task and output names for
the filter can be changed.
o
Right click table, select Filter and Sort.
o
Go to Results tab, rename default task and output names to something
user wants. (Note: keep the word “WORK.”).
4. If user users Query Builder, the task and output names for the filter can be
changed.
o
Right click table, select Query Builder.
o
Rename default query and output names to something user wants. (Note:
keep the word “WORK.”).
5. User can search a keyword within a column.
o
Highlight the column by clicking the heading of the column.
o
Ctrl + F to type in the keyword for searching.
6. User can go to a particular cell within a column.
o
Ctrl + G to select row and column to look at.
7. Save Enterprise Guide job regularly.
8. If user encounters issues with Enterprise Guide such as Enterprise Guide
crashing or not wanting all transformations to run, user can Stop the process.
9. User can select rows from a table and use Ctrl + C to copy the selected rows.
10. It is preferable to use Query Builder for joining multiple tables.
11. User can change the workspace layout by choosing View-> Workspace Layout.
This allows user to see the content of the table and/or with the flow.
12. User can choose to open the data of a table every time a table is selected, or to
open the data after all required tables are added to project.
o
Go to Tools -> Options.
o
Go to Data panel, toggle “Automatically open data when added to
project”.
[email protected]
30