We’re smarter together. Learn from this collection of community knowledge and add your expertise.

Filtering Every Which Way in SAS Enterprise Guide - July 11, 2017 webinar recording, slides, Q and A

by SAS Employee megak8 ‎07-12-2017 05:31 PM - edited ‎07-12-2017 05:33 PM (1,425 Views)

Here are the Q&A, slides, and recording link from the July 11, 2017 Ask the Expert webinar (Filtering Every Which Way in SAS Enterprise Guide).


Recording: https://sas.webex.com/sas/lsr.php?RCID=c60f210c82354571969f002b56720cf2  




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 (actual, forecast, residual) were derived from the values of the column that was split (type); they are now included in your new output data. 


Can you show one more time how to get to advanced expression?­

From the Query Builder, click computed columns, New -> Advanced expression­.


I don't see "Excel" as an option in Tools, Options, Results General.  Is that because I'm using only SAS EG 6.1?­

­Yes, that feature became available in Enterprise Guide version 7.12­ http://support.sas.com/documentation/cdl/en/whatsnew/64788/HTML/default/viewer.htm#p0q1rc7ws0dfwmn1q...


If you stopped and ended SAS right now, ­would the code be embedded? Is it embedded in the .egp file then?

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. 


To save the code generated, you'd need to create a task template then? ­

­No, you can choose the code tab and save the code from there.


The join -- it checks for variable name and type but does it check for length too? ­ 

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.­


What is common variable between files? ­

The commonly named variables with the same data type­ – usually the primary and foreign keys.


I noticed that in the variables presented, the variable names are different from file to file. ­

The query builder does not require commonly named variables; that’s the default for joins. It uses SQL code behind the scenes. (SELECT).


Do you need to specify paired quote marks around character variables then?

No, you shouldn't need to do that if you are comparing the values of the variables and not text strings.


In the filter & sort utility, you can query for data in a list.  Can you do the same thing in the query builder?­

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.


­How would you be able to see two or more dataset windows side by side?­ View - Side by Side only is allowing to see the Process Flow Window and One dataset. How do I display side by side 2 datasets?­

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.


What is the difference between an .egp file and a task template then? ­Is a task template smaller than an .egp project file?

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.


Say you needed to save the transpose task as a separate .egp file  - how would you do that?­

Make a copy of the .egp file and remove other tasks.


Please address WHERE vs IF for subsetting within the DATA step.­

A: ­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.  W­HERE is only appropriate for SAS data sets.­


Every time I run a program, SAS automatically generates links to working data sets under Process Flow, how to remove them?­

SAS Technical Support (support@sas.com) 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.


Is the underlying SAS code for all the tasks done with the wizards available to view to the user?­

Yes, you can select the code tab and access/edit the code from there.­


What's the difference between PROC TRANSPOSE and the SPLIT COLUMNS function you just used?­

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.­


If you write a program in SAS Enterprise Guide, does SAS Enterprise Guide create a Process Flow for you, even if you didn't use the wizard and just used the code?­

­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.­


We have a column that is delimited that looks like this­: actual_arrival|<null>|01/19/2017 0700^actual_departure|<null>|01/19/2017 1000^status|A|D­. Is there something that can easily parse, then make this column transposed,  the column goes field name, pipe, from value, field name, to value carrot to next field­?

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.­


­In query builder, the replace function, like create a new column where 1 will be replaced with 01; I know how to do this one at a time.  Is there a way to copy columns of data in the replace function? ie 1, 2, 3 with 01, 02, 03.­

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.


What is the macro looking portion (%let ...) used for when the code for a function is generated? (most of my programs only use code to pull from Oracle and DB2, so I only use the "meat­ of the code?”)

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.­


Do you recommend SAS programmers to switch to EG as programmer interface? What are the pros and cons? Thanks!­

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 .


How could I use the OH and IN query to create a data file that had one row per customer, with the last date of purchase and the total quantity purchased? Thank you!­

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).


Can you show how query builder will combine/create new variables?

This topic was addressed during the second half of the webinar.


Is there an easy way to combine multiple reports onto a single result page when you build a custom report in Enterprise Guide? We're using E-Guide to build some simple dashboards - 5 or 6 reports. It would be nice to build all the reports onto a single output page rather than stringing a bunch of pages together. I realize not really the topic of this session, but a link to some sample code or sample output or other webinar that tackles this topic would be really helpful. We do not have SAS Visual Analytics, just Enterprise Guide 7.1.

The report builder in Enterprise Guide will likely be a good fit. It’s demonstrated in the on-demand recording here and also in Enterprise Guide’s help https://www.sas.com/en_us/webinars/deeper-dive-enterprise-guide-reporting/register.html



Your turn
Sign In!

Want to write an article? Sign in with your profile.