Hi On first sight you are trying to build a "query builder" is there a reason for that? It is standard functionality in Eguide and AMO. With SAS-v8 SAS/intrnet there was a sample doing that.  If that is what you want to build, it should be possible. I'm more than happy for a better way of doing what I'm trying to do... We have 3 fact tables, the largest about 30M records.  The entire library is a SPDE library, with "appropriate indexes".  We have numerous dimension tables varying in size from 50 to 1M records, with the appropriate business and surrogate keys in the fact tables to join the dimension data.  Our server is of "moderate" specifications, not the fastest in the world, but not a dog either. A query against the fact table, especially when using the "provider" variable (a particular variable with low cardinality) is surprisingly fast (5-10 seconds).  The end users are almost always interested in a particular provider.  A typical query will be "provider = '12345' and date=<year to date | month to date>". My end users need to get to the non-summarized, detail level data very fast.  They also need the data in Excel for further processing, so I will be using AMO.  The end users are very smart, but not programmers, not the most SAS literate, and AMO rightly does not contain a query builder for this category of end user (runaway query), only the Filter and Sort functionality against a single table. If all they wanted to do was query the fact table, the built-in "Filter and Sort" capability of AMO would do fine.  But, they may want "provider = '12345' and date = <month to date> and state = 'NSW'".  In that scenario, I want to filter the fact table, then use a hash object join for the address dimension data, then use a subsetting IF for state = 'NSW'.  To do this, I will use a stored process (STP). Our end users often complain that our "canned" STP's either contain too many or too few pre-selected columns.  So, I want to give them the option to select their desired output columns. I further would like the end user to be able to paste subsetting information into Excel cells (esp. the provider), then use VBA to set the STP prompt and refresh.  It's unfortunate that the VBA interface to AMO is so limited (only single value text prompts), but I may be able to use hidden prompts and some fancy parsing to work around this shortcoming. Again, this needs to be FAST, both in the user interface and in query execution.  We cannot create a completely joined fact and dimension uber-table (which would be fast); it will be too huge, take too long to build, and take too much disk space.  I can't use a view (which would be slow), as the where clause passed to the view does not take advantage of the indexes.  I need to generate the code on the fly for the best performance. Ok, so with all that painful detail out of the way...if there is a better approach, I'm all ears. The old-days codings Counters/macros The naming convention for automatic is as described. In that approach it is  easy to contruct do-loops in SAS macro-language. By the way it is similar to REXX-language. I'm quite familiar with macro programming.  In my previous post I had attached my current code - you can have a look at it if you wish. Using sashelp.vlibname vtable is terrible slow. It was already that slow in 8.2 . It is not advisable to use them when good response is needed. See my previous reply to Quentin.  Even with a small, static table, using data retrieved from the workspace or stored process server was really slow, vs. data retrieved from the metadata server (i.e. datasource and datasource item). With SAS 9.4 “proc delete”has got his revival http://support.sas.com/resources/papers/proceedings13/022-2013.pdf . Sure, but I'm not sure what this has to do with sashelp.v* tables slow performance??? Proc datasets is already much better in performance than using sashelp.v- approach. Yes, I usually use PROC CONTENTS, but this won't work for the datasource for a dynamic text prompt, which displays BEFORE the stored process code runs. What are your options:  - You could use a “libname _all_  list”  with redirect and “proc datasets” to improve speed.  - you could set up less or more permanent libraries containing the libnames / tables information.  It would make sense to use a alike structure as the sashelp.v- members   - If the expected tables are less or more stable you could make own copies of the sashelp.v- members in a sort of caching approach.    Using the prompting framework you can use table-input when those tables for the prompts are registered in the metadata. That is asking for a more stable table-layout describing them. Only if I do this BEFORE the STP runs, not as part of the STP.  Which is OK, since the dataset attributes would rarely change.  But see previous reply to Quentin (as well as my second post) - this approach still ran slowly for me. Using the prompting framework with table-input and having many app-servers or using to want a SP-server can give you some surprises. These are:   The menu for prompting will open up always a WS-server even if your process has been associated with a SP-server or other logical-server. Hmmm, well that's a bummer.  Who designed that?  If I say I want my STP to run on the STP server, why open a WS server?  This could be part of the reason for my poor performance. When more app-servers are available for the user and libname, the prompting framework will not necessary use the same app-server as you have defined your process to run. It will pick a technical first one in an internal invisible list. Thanks for the info.  Not applicable in my case, but useful to know. These kind of not logical, not documented behavior can make your solution unsolvable. Yep...although I'm leaning toward the datasource and datasource item approach for UI performance reasons. Just in the case you access to one possible view it is reliable functioning. I classify this as a design/implementation failure although SAS won’t admit that. I agree.  SAS R&D are incredibly brilliant but 1) I don't think they always see things from the end user perspective, 2) often do similar things in an inconsistent manner (see my reply to Vince), and 3) sometimes create solutions where you can work wonders...IF you can figure out the hoops to jump through to get there.  I believe some analyst reports ranking SAS as really useful, but difficult to use, have some basis in fact (this from someone who has worked with SAS for 25+ years, and has based most of his career on SAS programming).  Perhaps it gives some of us job security Sorry Vince, I could not resist this opportunity, but I guessed you already expected that. The reference you have given before is a good one, of course...    Vince's input was incredibly useful - see my reply ... View more

As a quick aside, and drifting a bit off topic (hijacking my own thread )... I'm certainly no security expert, esp. with respect to metadata, but: 1) AFAIK, currently you CANNOT have a completely secure environment without host operating system security, i.e. NTFS permissions.  IOW, if someone can "connect" to the server, knows the physical path to the library, and the library and its contents have NTFS Everyone: Read permission, and the datasets don't have a password on them, then a knowledgeable end user can get access to the data. 2) I can't help feeling that fine grained access control would be better implemented at a "lower level" than the metadata server, i.e. via the engine and dataset header.  Without too much detail: * For each engine (eg. BASE, SPDE, etc), the engine determines whether the particular path can be allocated. * For each dataset, the dataset header determines whether the table can be accessed, which columns can be seen, and which rows can be seen. Perhaps the engine and dataset headers communicate with the metadata server, or perhaps they read security information some other way, say some table that is updated via some GUI.  I would like to see more fine grained security available in just an old-fashioned Base SAS install, without the need for O/S level security. ... View more

Hi , While I'm happy writing stored processes, I tend to hand write the source code for them.  And in that code I rarely uses a metadata library.  I tend to use base engine libraries or whatever engine I need for a data source.  Many of my datasets are never registered in the metadata.  Some down sides of this approach are that I don't have all the security benefits of libraries/datasets defined in the metadata.  (But I'm okay with that, since I can still control access to the stored process itself, and I haven't been working with sensitive data).  And if one day we move servers, updating the metadata libraries won't point my stored process toward a new physical locations of the data. (But I'm okay cuz most of the locations/dataset names in my code are dynamically derived from macros or other project data).  But the BI framework is obviously centered around metadata.  With lots of benefits beyond the two I've mentioned. All our libraries are pre-assigned (no, I didn't come up with this policy).  And our batch jobs use the -metaautoresources "SASApp" option.  So, all our libraries are assigned, whether in EG (Workspace Server), Stored Process Server, or batch job.  We do use security to restrict access (allocation) to particular user groups.  I'm happy to use a metadata library allocation, i.e. the meta engine, but there is no need. So as an old SAS school programmer, if I had a &data prompt, I (like you) would want a simple prompt to select a libname (or physical path), data set name, etc. Like SAS/AF or whatever.  And were I to have a problem like yours, I would likely approach it as you did.  (Except that sashelp.vcolumn is so big, I'd probably have a separate nightly job to make small datasets for these dynamic prompts). I made a snapshot of sashelp.vcolumn for a single library.  It reduced the number of columns from say 40,000 to 4,000.  Still a lot, but filtering a list of 4,000 columns should be quite quick (depending on the server specs).  It still had very poor performance with respect to the initial GUI display. After the user has selected the datasource and datasource items, my STP code needs to know which datasource (SAS dataset) and datasource item (SAS dataset column) the user selected.  The default SAS macro variables created by the prompt manager does not provide this information (but it can be derived via Vince's macro). But since the entire BI framework is about metadata, it's not surprising to me that when you use the data prompts, you get metadata information.  The BI approach is trying to get folks to do more with metadata, and less with libnames/data set names written in code.  See for example recent addition of metadata-bound libraries.  http://www.notecolon.info/2012/11/note-metadata-bound-libraries.html   I guess the mantra from that perspective would be: more metadata usage means more security and easier server administration and easier usage for business analysts. Maybe.  I still assert that displaying the metadata path to the end-user via the baked-in GUI elements is not good, esp. without giving the STP developer the chance to hide the path.  I just want the user to pick a column!  S/he could care less about the path to the metadata table object. As for your request for an example where knowing metadata source path for a dataset would be useful to a stored process developer, I guess it depends on how the metadata paths are set up.  For example, if metadata paths were informative, such as Data/Dev/DataX vs Data/Prod/DataX, then I can see how I might want to know the metadata path for the dataset selected by a user, so that I could determine whether they are analyzing /Dev data or /Prod data. Our metadata paths do show this information.  I still don't want to display the path to the end user.  The user will know the environment s/he has selected by the library from which they selected the datasource.  And the environment (Dev or Prod) is not dependent on the metadata path.  If I move the table objects to path foo and bar, it will still be Dev or Prod metadata.  So I don't like the idea of binding the environment to the metadata path. ... View more

Hi, Thanks for the replies so far.  Perhaps it will help to explain what I'm trying to do... I'm trying to develop a generic approach to creating an SQL query against a dataset using a stored process.  In most cases, it will be a straight query against a fact table.  In some instances, I will then join dimension data to the fact table subset using a hash object join.  For now, assume that the subsetting (where clause) only occurs against the fact table. In some instances, the end user will select the libref and dataset.  In others, I will simply set default values for these parameters, then make the prompts hidden. So, in the user interface (prompt manager), the end user selects:  the data source (libref and dataset) (FROM clause), desired output columns (SELECT clause), subset criteria (WHERE clause), and output sort order (ORDER BY clause - for now only ascending sort order is supported). The best (albeit klunky) design I've been able to come up with so far is: libref:  text, dynamic list, single value dataset:  text, dynamic list, single value columns:  text, dynamic list, multiple ordered values filter name/value pairs (5):  name:  text, dynamic list, single value  value:  text, user enters values, multiple values  (5 pairs of prompts) additional filtering:  text, user enters values, single value order:  text, dynamic list, multiple ordered values The dataset list is dependent on the libref selection, all downstream column selections are depended on libref and dataset selection. The source dataset for the dynamic lists is SASHELP.VLIBNAM, SASHELP.VTABLE, and SASHELP.VCOLUMN.  I created the metadata for these tables by creating a pre-assigned library definition for SASHELP, then imported selected tables, writing the objects to \Shared Data. The user selects the desired libref, then the desired table, then the desired output columns.  For the filter, there are 5 "built-in" prompts where the user selects the desired variable, then enters the desired subsetting values.  Some of these variables have about 1.5 - 3M distinct values, so "Get Values" from a table simply won't perform in an acceptable manner - the user will have to type or paste the values into the prompt.  The variable data type is checked in the stored process, and the data is quoted as appropriate.  In almost all cases, 5 prompts (variables) are sufficient.  Otherwise, an advanced user can augment the filter (where clause) using the additional filtering prompt.  If 5 prompts is more than enough, I can simply hide the unnecessary prompts without having to change the underlying STP code.  If I want to "bind" a particular variable to a prompt, I can set a default value and either protect or hide the variable selection dropdown.  This approach (hopefully) allows flexibility for the end user and fast stored process development for the developer. This all works fine...EXCEPT it performs horribly.  Like 1-3 minutes for the prompt manager to display the UI.  So I tried making a "snapshot" of VLIBNAME, VTABLE, and VCOLUMN for a single library, but still got very poor performance.  My guess is all these dynamic, dependent prompts, plus having to query the workspace server, is the cause of the poor UI performance. So, I switched to using the datasource and datasource item prompts.  Which performs great - it's reading from metadata instead of dynamic views on the workspace server - but these prompts IMO set really stupid values for the macro variables!!! 1) What I meant by "derive" is, can I use PROC METADATA or the metadata data step interfaces to derive the libref from a metadata table object, if I have the path to the object?  The SASHELP table metadata objects are in Shared Data only because I wrote them there. 2) I understand that DATASOURCE is the metadata location - that was in fact my point.  And I wasn't just "griping" - most (all?) of my stored processes are "old school" - I'm not doing much with say PROC METADATA, PROC STP, etc.  I truly don't understand whether any stored process developer, or stored process end user, would be interested in the metadata path over the libref.dataset name of the table object?  I'm only working with Tables, not say Information Maps.  Would the metadata path be of interest for an Information Map datasource?  If anyone can provide an example of a stored process where the metadata path is useful to the stored process, I'd love to see it; I'm sure I would learn something from the example. <quote>DATASOURCEITEM allows user to select data items from either tables</quote> I disagree, since the DATASOURCEITEM does not contain sufficient information to derive the source libref and dataset.  If I have SASHELP.SHOES, SASUSER.SHOES, and SOMELIB.SHOES, which SHOES dataset did the end user select?  The generated macro variables don't give me that information. 3) I can't hide the DATASOURCEITEM GUI from the user, since I need it to be visible so they can select the desired columns.  What I want to suppress is the DATASOURCE path in the GUI, since it is of no interest (and in fact confusing) to my end users. 4) IMO this is a bit short sighted.  Setting the available list of downstream DATASOURCEITEMS based on the selection of an upstream DATASOURCE is obvious, isn't it?  Surely R&D would get this???  We were doing this in SAS/AF for decades!  In DMS, select Tools --> Query to see what I mean! 5) OK, thanks, COLUMN0 is deprecated then, but still around due to legacy functionality. I would open a TS track, but I think this is more of stating a desired use case and request for feedback from the community.  If others are in agreement I'll formalize this as an enhancement request (although IMO a few of these are not enhancements but design bugs worthy of a hotfix). I understand the evolutionary history of SAS, but the subject matter in this post is very recent code/functionality in SAS. Like I said, maybe I'm missing something, but I think the prompt macro variables should be returning the libref.tablename.columnname rather than a metadata path. I wonder if the prompt manager got much usability testing by end users before being released to the market? ... View more

I've attached a macro I use to determine the number of observations for various scenarios.  Perhaps you'll find it useful.  You'll have to modify it to remove the call to %parmv. ... View more

Try these, then ask follow up questions: https://www.google.com.au/search?q=google&oq=google&aqs=chrome.0.0j69i65l2j0j69i65j0.1088j0&sourceid=chrome&ie=UTF-8#q=what+is+powershell Windows PowerShell - Wikipedia, the free encyclopedia ... View more

Hi, More Googling revealed this Microsoft Excel Application entry missing in DCOMCNFG .  I followed tibb's recommendation, found Microsoft Excel Application, and made the changes as recommended by Dave, i.e. changed the Identity tab to use <machinename>\sassrv for DCOM. It's working now, thanks so much for your help!!! Scott P.S.:  Dave, if your PS script doesn't contain private IP, I'd love to get a copy of it.  I'm always looking for more PS examples! ... View more