Specifying a Dynamic List based on Prior Input

[djbateman]

First off, I am very new to SAS EG.  I have written a macro in SAS that simply converts a SAS dataset to an Excel file.  I want to start using EG because of the user-friendly prompts that will make it easier for the data managers to use.  One of the prompts asks for the location of the SAS data library.  Based on the datasets available in the library, I want to create a dynamic list from which the data manager can choose which dataset they want to convert to xls.  Is this type of post-processing available in EG?

[DaveShea]

Welcome to Enterprise Guide, its a great place for working and learning.

Having said that, I am pretty sure that what you want to achieve may not available from SAS Enterprise Guide without you having access to a Metadata Server in the background, and even then you may need to compromise a bit.

In my idea, you would create a regularly updated dataset that contained a single column (LIBREF_and_MEMNAME) which would be the concatenation of the LIBREF and MEMNAME of each of the datasets you wanted your users to be able to choose form. The concatenated value might be "SASHELP.CARS" or "MYDATA.SALES_Q1" (note the <dot> between the two parts). This single column would be used to populate a dynamic list in a Prompt and your users could choose a Libref/Dataset combination from the list to export to Excel.

The Enterprise Guide Prompt can expose the user's choice as a macro variable to the rest of the SAS session (e.g macro variable &MyChoice resolves to MYDATA.SALES_Q1).  Make sure the [Use prompt value throughout project] box is ticked on the General tab of the [Edit Prompt] dialog box to allow your macro variable to be available in later steps.

The macro variable could then be used to drive a Proc Export to Excel or whatever method you are choosing.

I fully realise that the DICTIONARY.TABLES (SASHELP.VTABLES) views can provide dataset information about datasets currently available to the SAS session, but the need for the prompting framework within SAS Enterprise Guide to use datasets registered in Metadata prevents their use, so far as I know.

The important bit to know is that the LIST_OF_TABLES dataset needs to be registered in Metadata in order for the Prompt to be able to read from it.

At this point your eyes may be glazing over at the mention of the M-word (I know mine are!), but hold tight, this might be good, however if your site has not implemented a Metadata server, this might be the time to bale out of this post.

The LIST_OF_TABLES dataset needs to be regularly updated for it to be of use to you and your users, of course this also means that you are in control of the datasets that you want your users to export which can be handy.

To be honest, I started this post off thinking this was not going anywhere, but there may be the nut of an idea in there for you. However, I am sure, ChrisH@SAS will swing past some time and add his ideas.

I hope that this helps, or at least provokes you to experiment a bit more.

Cheers,

Downunder Dave.
Always prepared to be proven wrong.