Did you miss the Ask the Expert session on Filtering Every Which Way in SAS Enterprise Guide? Not to worry, you can catch it on-demand at your leisure.
By the end of this hour, you will know the secrets to exploiting key data management, integration and filtering capabilities of SAS Enterprise Guide. We’ll cover:
Here are some highlighted questions from the Q&A segment held at the end of the session for ease of reference.
The new variable names (actual, forecast, residual) were derived from the values of the column that was split (type); they are now included in your new output data.
From the Query Builder, click computed columns, New -> Advanced expression.
Yes, that feature became available in Enterprise Guide version 7.12 - What's new in SAS Enterprise Guide?
The code that performs the subset is added to the code that is generated. There is no code stored by default; the query builder will generate the code when it is run again. If the code is part of the project, it will be stored as part of the .egp file. You can verify that it is part of the project by looking for the icon that identifies code.
No, you can choose the code tab and save the code from there.
The default for joins is a common name/type. I do not believe it checks for length. It will take the length of the first variable that it encounters at compile time.
The commonly named variables with the same data type – usually the primary and foreign keys.
The query builder does not require commonly named variables; that’s the default for joins. It uses SQL code behind the scenes. (SELECT).
No, you shouldn't need to do that if you are comparing the values of the variables and not text strings.
Yes, you can do this from the filter tab in the Query Builder. This operation was shown during the first 30 minutes of the webinar.
From the top margin, view -> workspace layout -> side by side. Double click on the 1st data set. Choose side by side, then go to the empty tile and select the next data set that you want to see. You may need version 7.1 or later.
Task templates allow you to use the same settings for a particular task. When you run a task, such as the Bar Chart task, you might want to frequently use the same settings. Instead of opening the task multiple times to specify these settings for different input data sources, task templates enable you to save your settings for a specific task to a template. You can then run that template with any input data source and achieve more consistent results for that task. .egp files are Enterprise Guide projects – a holistic recipe of ingredients (data) and assembly instructions (tasks and code) for achieving repeatable results/outputs. They contain process flows, programs, tasks, and all the instructions for taking your data from raw and operational to intelligent and analytical.
Make a copy of the .egp file and remove other tasks.
The WHERE acts like a pre-processor and goes to the SAS data to perform the subset before any other processing happens. The IF requires that the data be loaded into memory and can then be subset. IF is appropriate for raw data as well as SAS data. WHERE is only appropriate for SAS data sets.
SAS Technical Support (firstname.lastname@example.org) is likely a better resource to answer this question. In the query builder, and most other tasks that generate data outputs, you can specify where to save outputs. You’ll need write permissions to create permanent outputs.
Yes, you can select the code tab and access/edit the code from there.
They both use PROC TRANSPOSE behind the scenes. The SPLIT COLUMNS task generates most options for PROC TRANSPOSE; coding PROC TRANSPOSE yourself or revising the generated code give you more control.
There is still a process flow, though you do not have to use that as part of your visual. You can simply use the program editor window to process the code.
If the value is a character value, then you could possibly use the SCAN function in order to separate the values into different components. You would have to specify the separator. If you create separate variables as the result of SCAN, then you could use TRANSPOSE. Please experiment to make sure these functions give you the output that you want.
DATA step (if), formats and/or lookups are likely good fits for this operation. You could also use the dta editor in Enterprise Guide if you have write permissions.
Enterprise Guide often adds extra code that we might not see if we wrote the code ourselves – all code generator technologies do this to some extent because they retrieve pre-defined specifications to achieve consistency. If you are capturing the code for subsequent use, you may want to remove the %let and other statements that have been added by Enterprise Guide.
Enterprise Guide gives you a modern, sophisticated Windows programming interface for server environments. It has dozens of additional amenities, such as the process flow and hierarchical views for creating repeatable, scalable processes, visual documentation, scheduling, syntax help, grid-enablement, editing data, and more such as Ten SAS Enterprise Guide program editor tricks .
This topic was addressed during the second half of the webinar. Recency, frequency and monetary analysis is likely a good fit too. (Tasks -> Data Mining -> Recency, Frequency, and Monetary Analysis).
This topic was addressed during the second half of the webinar.
The report builder in Enterprise Guide will likely be a good fit. It’s demonstrated in the on-demand recording here and also here, in Enterprise Guide’s help
I often have issues with case sensitivities when joining data. What is the most efficient way to overcome this.?
Case should only be an issue if you are comparing text values. You could choose to use the UPCASE or LOWCASE functions to help make consistent comparisons to text.
Can you give me a method to eliminate duplicate records? I have tried dupkey and it was taking out records that should have been kept.
There is a DISTINCT keyword that is available with SQL. In the Query Builder, click the Select Data tab, and select the Select distinct rows only check box.
Do you know if, when outputting to PowerPoint, can you then edit the slide from within PowerPoint?
Yes, you can edit just as you would other content in PowerPoint.
What are the new variables that were created in the split column example? What is the best way to drop those?
The new variable names were derived from the values of the column that is being split. That is now part of your new data, so I wonder why you would want to drop them?
If you show the output again, you'll see that the far right 3 columns are all blank with obscure names. I would not want them as part of the new dataset.
Those are values such as confidence level values L95, U95 that are included in forecasts.
Can you show one more time how to get to advanced expression?
From the Query Builder, select computed columns, New -> Advanced expression.
Want more tips? Be sure to subscribe to the Ask the Expert Community Library to receive follow up Q/A, slides and recordings from other SAS Ask the Expert webinars. From the Ask the Expert Library, just click Subscribe from the orange bar underneath the list of the recent articles.
NOTE: For best results when opening the attached slides, click on the “download” icon.