SAS Enterprise Guide using 1 query on multiple data sets

Question

I want to run multiple data sets (that are in the same format) through a single query and for it to produce an output for each data set that is input.

Is the possible in SAS Enterprise Guide?

Answer 1

For most tasks in EG, you can right-click them and choose "Select Input Data...", and change the input data. The filter & query task, which I'm assuming you're talking about, does not have this option.

The only approaches I can think of to re-use a query involve delving into SAS code. If you're comfortable with this, here's one suggestion (this is in EG4.1, I presume later versions are similar):

1. Create your desired query from your first data set using the filter and query task.
2. Save & close the filter & query dialog.
3. Right-click the query task and select "Add as code template". A new code object will appear in the project designer. When you open it, you'll see the SAS code that was built by the filter & query task.
4. In here, change the input data (it will be in the from clause of the SQL code) to a different input data set, and change the name of the output data (see create table xxx as). You may also need to change the name of the output data where it says %_eg_conditional_dropds(xxx) or similar - this part is dropping a dataset with the same name as your output data, if it exists.

It's all a bit laborious - if you're doing a lot of this, I suggest you hop into SAS coding, because there are lots of ways to do what you're describing that are much easier than hacking EG task code :)

Answer 2

Write the sas code to loop through the datasets executing the sql on each one and naming the results with a unique identifier. Not fun the in point and click world.

Answer 3

Not too sure how many datasets you need to run this for at a time but perhaps you could use a dynamic prompt?

http://www.google.com/url?sa=t&source=web&cd=1&ved=0CBUQFjAA&url=http%3A%2F%2Fsupport.sas.com%2Fresources%2Fpapers%2Fproceedings10%2F041-2010.pdf&ei=khmjTeClJ4iCsQPV7PX5DA&usg=AFQjCNFotS3cbwYl5kCCpQUfqKYK3ndflQ&sig2=3ItVrgWXlegBzvjt4-rGAQ

Alternatively, attack it from a different angle by appending all of the datasets together then split into the different outputs once you get to the reporting side...