Thanks Tom.  I'm hoping someone knowledgeable (either SAS staff or end user) will chime in.  I don't have time to hack the XML, then reinstall EG with each change until I get the desired results. I would have thought this issue would have been found and fixed during the SAS QA testing?  Surely this would should up on the first test or so??? ... View more

Hi Reeza, Thanks for your reply... 1) I created a data source prompt, but what I get in the SAS code is the metadata path.  For example: DATASOURCE_1=/Shared Data/CLASS(Table) DATASOURCE_1_TYPE=1 Um, I'm gonna need sashelp.class somewhere in my code.  /Shared Data/CLASS(Table) is less than helpful. 2) I created a data source item prompt, multiple ordered values, allow users to select from multiple data sources.  The user interface looks pretty promising.  However, what I get in the SAS log is: DATASOURCEITEM_5=Name DATASOURCEITEM_50=5 DATASOURCEITEM_51=Name DATASOURCEITEM_52=Sex DATASOURCEITEM_53=Region DATASOURCEITEM_54=Product DATASOURCEITEM_55=Subsidiary DATASOURCEITEM_5_COUNT=5 DATASOURCEITEM_5_PATH=/Shared Data/CLASS DATASOURCEITEM_5_PATH1=/Shared Data/CLASS DATASOURCEITEM_5_PATH2=/Shared Data/CLASS DATASOURCEITEM_5_PATH3=/Shared Data/SHOES DATASOURCEITEM_5_PATH4=/Shared Data/SHOES DATASOURCEITEM_5_PATH5=/Shared Data/SHOES DATASOURCEITEM_5_SOURCE_TYPE=1 DATASOURCEITEM_5_SOURCE_TYPE1=1 DATASOURCEITEM_5_SOURCE_TYPE2=1 DATASOURCEITEM_5_SOURCE_TYPE3=1 DATASOURCEITEM_5_SOURCE_TYPE4=1 DATASOURCEITEM_5_SOURCE_TYPE5=1 DATASOURCEITEM_5_TYPE=1 DATASOURCEITEM_5_TYPE1=1 DATASOURCEITEM_5_TYPE2=1 DATASOURCEITEM_5_TYPE3=1 DATASOURCEITEM_5_TYPE4=1 DATASOURCEITEM_5_TYPE5=1 Um, what I NEED is something like: DATASOURCEITEM_51=SASHELP.CLASS.Name DATASOURCEITEM_52=SASHELP.CLASS.Sex DATASOURCEITEM_53=SASHELP.SHOES.Region DATASOURCEITEM_54=SASHELP.SHOES.Product DATASOURCEITEM_55=SASHELP.SHOES.Subsidiary or something like that.  And isn't this rather like database column syntax that's been around forever?  It's not like SAS even needed to reinvent a new syntax.  THIS I could parse in macro code! For the end programmer trying to actually DO something with the results (macro variables) of the prompting mangler, /Shared Data/CLASS is pretty useless.  Whoever dreamed up this syntax wasn't a SAS programmer, but I would have thought SAS would have done some usability testing before releasing this (prompting mangler) on their users. If someone is aware of a way to translate a macro variable containing the metadata path to a table definition, to the libref and memname of the table that it references, please let me know.  A pointer to the doc example would be fantastic. So, for now I've defined in metadata tables from the SASHELP dictionary views, i.e. VSLIB, VSTABLE, VTABLE, VCOLUMN, etc, and bound them to dynamic list text prompts.  VSTABLE depends on VSLIB.  VCOLUMN depends on VSLIB and VSTABLE, etc. 3) I'm not sure how to make it clearer???  I want the same or similar functionality available to the end user of a stored process as that available in EG or AMO Filter & Sort task, Filter tab, i.e. a point and click, easy to use way to generate a dynamic, syntactically correct where clause based on the source dataset. 4) I'm happy to hard code the joins in the stored process. It's the where clause against the fact table that I want to be dynamic and flexible.  I'll probably use a hash object join to link in dimension data rather than SQL anyway, which is easier syntax to generate in the stored process. Finally, I have come up with something "interesting", but not sure if I'll use it or not.  Performance is a major consideration, especially with a large number of rows, as well as the overall user experience. I'll describe it here.  If you have AMO, you may want to play along... In Excel, use the SAS Data task to drop a table on Sheet1.  I used SASHELP.PRDSALE. Use the built-in Filter and Sort menu to drop columns, change their order, add a where clause, and sort the results.  Click OK and view the results.  Note:  This will create the Named Range Table1. I've created a stored process "Create Dataset from Excel Table".  It has 5 text prompts:  InLib, InData, OutLib, OutData, and Return (return the dataset to Excel?  No=0, Yes=1) InLib and InData MUST match the table selected for Sheet1, i.e. SASHELP.PRDSALE.  This is needed to create the correct variable attributes of the uploaded dataset.  (Hmmm...I may be able to get the dataset name from the DataView object name (SASApp:SASHELP.PRDSALE) and use VBA to set the parameter of the stored process (so perhaps make it a hidden parameter).  This would be an improved user experience.) The stored process has an input stream defined as xl_data, text/xml, rewind. Drop this stored process on Sheet2.  When you initially drop the stored process, it will ask for the input stream.  Enter =Table1[#ALL]. Here is the code for the stored process: %macro CreateDatasetFromExcelTable; libname xl_data xmlv2; * get variables from instream dataset ; proc contents data=xl_data.&_webin_sasname out=work.contents (keep=name varnum) noprint; run; proc sql noprint;   select name into :vars separated by " "   from work.contents   order by varnum   ; quit; data &outlib..&outdata;   format &vars; %* set PDV order based on Excel table ;   if 0 then set &inlib..&indata (keep=&vars);  %* set variable attrs based on source table ;   set xl_data.&_webin_sasname;  %* set data values based on Excel table ; run; %if (&return) %then %do;  %* all of this is for debugging ; proc print noobs; run; proc contents data=&syslast; run; %end; %mend; options mprint nocenter; %CreateDatasetFromExcelTable This does an OK job of replicating the dataset that was filtered on the server by the SAS Data task and returned to Excel.  I could then "chain" a 2nd stored process to work with that "uploaded" dataset and join in additional data, summarize data, etc. But again, if I could generate a decent where clause from the stored process prompting mangler I would skip the SAS Data process altogether.  Performance is a concern since 1) the query returns the result set to Excel (probably via XML under the covers, which gets parsed by the AMO addin, and 2) I turn right around and pass the result set back as XML for the stored process to create the same dataset on the server. If there was a way I could "trick" the SAS Data process to 1) create the result set as a dataset on the server, and 2) not download the result set back to Excel, I'd do that.  But, #2 defeats the purpose of the SAS Data functionality, so I can see why that's not baked into the product. Thoughts? ... View more

Jaap Karman wrote: To create the table is the first step. Making it dynamic is the next one. This is requiring all the registrations within the metadata (tabels/fields). Information map studio etc. That part can be given and is needed as the dynamic prompts. By the way, Is te usage of information maps not giving something usuable for you? It is meant to deal defining views on data. Limiting views on tables/colums looks something you are needing. It looks that you are trying to rebuild the query manager as available in EGuide but has additional limiting requirements. Compared to SAS/AF the application building has moved to adding a  lot of metadata definitons. It means thinking developing aside from coding flow-diagrams and processing also with object/properties views and adding security at an other way. We don't have a full EBI license so we don't have Information Map Studio. Yes, I'm trying to replicate the query manager as in EG/Filter and Sort in AMO.  The query needs to be dynamic, not canned as in IMS or a hard coded stored process. I want to build a UI that will allow me to 1) select a data source, 2) select desired columns, 3) generate a where clause, 4) generate proc sql code, all within a stored process. e ... View more

Hi Jaap, Thanks.  Let me restate to make sure I understand: * create a table of metadata.  For example: proc contents data=sashelp._all_ out=work.columns (keep=libname memname name type) noprint; run; proc sort data=work.columns (keep=libname memname) out=work.tables nodupkey;   by libname memname; run; * then, use dynamic prompts plus dependencies to allow the user to select the library --> table --> columns I just had a play with data source item prompts, multiple items (or multiple ordered items).  I had not used these prompts before.  They appear to meet my needs re: selecting a column, but the entire architecture is a far cry from the where clause generator I need to create. I'd like to create the same interface as the Filter and Sort functionality, i.e. (excuse the ASCII art): <variable name> V (dynamic dropdown list)  || <operator> V (dropdown static list)  ||  <value(s)>  ||  [...] (get values)  ||  <AND|OR> V (dropdown) all on one line, combining each line to make a syntactically correct where statement.  And if not that, then something just as simple for the end user. Right now, all I can think of is hard coding the UI components to the "main" variables in each of the datasets to be filtered.  But I can see it now:  "End User:  Can you add this variable to the query?"  Pretty soon I've hard coded 50 or so UI elements to the dataset, times X number of datasets to query . I can do all the behind-the-scenes macro gyrations if I can somehow get the Prompting Mangler to generate my desired user interface.  (Which was oh so easy in "old" SAS/AF) Regards, Scott ... View more

Thanks...and thanks too to your parents for giving you such a great name :smileygrin: When I err, I often err on the side of too much information, rather than not enough.  So, perhaps sometimes my messages won't get read, or only skimmed.  But, with the time zone difference between Sydney and the US (where most of the answers usually originate), I take the chance of too much detail.  Otherwise, it can be days going back and forth clarifying the problem statement.  Or else I'm sending real time updates at 2AM my time... ... View more

This would not give the correct results. Try WHERE compress("   Compare Value   ") = "Compare Value" to see what I mean. ... View more

newbi wrote: Reeza, I'm using EG and strip() or trim() didn't not work. Create table test as SELECT DISTINCT t1.Modified,right(trim(t1.Modified)) as trim_Value, FROM Work.table t1; EG Code Generated for filter: Select * From test WHERE t1.trim_Value = '00000102     ' ; The right() function is suspicious.  This would right justify a character variable, which would create leading spaces, which *are* significant. Try this: create table test as SELECT DISTINCT t1.Modified,strip(t1.Modified) as trim_Value FROM Work.table t1; This should then work, regardless of whether EG is padding the generated values with trailing spaces - trailing spaces are not significant: Select * From test WHERE t1.trim_Value = '00000102     ' ; Of course, you could just use the function directly in the where clause: Select * From test WHERE strip(t1.trim_Value) = '00000102     ' ; But since that is EG generated code... I suspect your source data has character data that contains leading spaces.  Or sometimes non-printing control characters can be problematic; you could try reviewing your data using the $hex. format. Hope this helps... ... View more

Hi Patrick and Tom, Thanks for the replies.  Much appreciated.  Let me also take this opportunity to publicly thank you for your contributions to this forum.  I always learn a lot from your posts. We have a pretty standard managed environment/planned deployment, i.e. Lev1, Lev2, and Lev9 (test of metadata security). In Lev2, we have a number of concatenated libraries defined in metadata, as "test", then "prod".  All our libraries are pre-assigned, so they get allocated whether from EG, Base SAS (DMS), or batch jobs. In E:\SAS\Config\Lev2\SASApp\appserver_autoexec_usermods.sas: %put AUTOEXEC; libname usr          "R:\tier0\usr\&sysuserid"; libname usrspde spde "R:\tier0\usr\&sysuserid";  * wish we could use a macro variable in metadata defined libraries ; In E:\SAS\Config\Lev2\SASApp\sasv9_usermods.cfg: /* Run initstmt to reallocate concatenated libraries. */ -initstmt '%put INITSTMT;%realloc_concat_libs;' I've defined the previously posted library reallocation code as a macro for easier inclusion in an initstmt. Here is a partial log when I launch E:\SAS\Config\Lev2\SASApp\sas.bat: NOTE: AUTOEXEC processing beginning; file is E:\SAS\Config\Lev2\SASApp\appserver_autoexec.sas. AUTOEXEC NOTE: Libref USR was successfully assigned as follows:       Engine:        V9       Physical Name: R:\tier0\usr\sbass DEBUG=CACHE_DISABLE option set. NOTE: Libref USRSPDE was successfully assigned as follows:       Engine:        SPDE       Physical Name: R:\tier0\usr\sbass\ NOTE: AUTOEXEC processing completed. INITSTMT  (>>> No changes to the library allocations since the metadata library assignment has not taken place yet <<<) NOTE: Libref claims successfully assigned from logical server. ... NOTE: Libref clminfo successfully assigned from logical server. NOTE: Libref SASDATA successfully assigned from logical server. (End of library allocations from metadata.  About 30 librefs are defined.) I then submitted this from DMS: 1    libname claims list; NOTE: Libref=   CLAIMS       Scope=    DMS Process       Levels=   2         -Level 1-       Engine=   BASE       Physical Name= S:\tier2\test\claims       Filename= S:\tier2\test\claims         -Level 2-       Engine=   BASE       Physical Name= S:\tier2\prod\claims       Filename= S:\tier2\prod\claims Now I explicitly call the macro that was in the INITSTMT.  options nonotes nosource nosource2 nomprint, so no visible changes in the log. 2    %realloc_concat_libs; And now the libraries have been reallocated, but the *source* of the library reallocation was from the metadata.  IOW 129  libname claims list; NOTE: Libref=   CLAIMS       Scope=    DMS Process       Levels=   2         -Level 1-       Engine=   V9       Physical Name= S:\tier2\test\claims       Filename= S:\tier2\test\claims         -Level 2-       Engine=   V9       Access=   READONLY       Physical Name= S:\tier2\prod\claims       Filename= S:\tier2\prod\claims So, it appears to me that the order of initialization is autoexec, initstmt, metaautoresources.  I certainly wish it was metaautoresources, autoexec, initstmt, since I have more control with good-old handwritten SAS code (i.e. autoexec). I know we can tighten the NTFS permissions, and that may solve the issue.  But, I'd like to put as many fail-safe measures in as possible to ensure that our production libraries are safe from being accidentally overwritten.  I'll investigate the links you posted to see if there are additional steps I can take. (BTW, the problem occurred when we were testing a new scheduler, and the service account had been created incorrectly - too high privileges, which bypassed the NTFS permissions on the libraries.  Additional fail safes within SAS would have prevented the incorrect overwriting of the production data.) Thanks, Scott ... View more

Thanks Patrick. A.  Well I thought we'd done that, but a new service account that was an administrator submitted jobs in batch (testing from a scheduler) that incorrectly overwrote production datasets.  My intent is to add an additional fail safe to the mix.  (Yes, we can fiddle with the permissions on the libraries, but it gets a bit messy.  The additional fail safe would be useful.) B.  I need the process to be dynamic, so I wrote this code: %let options=%sysfunc(getoption(notes)) %sysfunc(getoption(source)) %sysfunc(getoption(nosource)); options nonotes nosource nosource2; proc sql noprint;   create table work._concat_libs_ as     select libname, path, engine, level   from     dictionary.libnames   where     level > 0 and libname ne "SASHELP"   ; run; filename temp temp; data _null_;   set work._concat_libs_;   file temp;   by libname notsorted;   if (level eq 1) then     put "libname tmp" level '"' path +(-1) '";';   else     put "libname tmp" level '"' path +(-1) '" access=readonly;';   if last.libname then do;     put "libname " libname engine "(" @;     do i=1 to level;       put "tmp" i @;     end;     put +(-1) ");";     do i=1 to level;       put "libname tmp" i "clear;";     end;     put;   end; run; proc delete data=work._concat_libs_; run; %include temp / nosource nosource2; filename temp; options &options; However, I'm running into a timing issue that I can't seem to fix.  The order of initialization seems to be: autoexec initstmt metaautoresources If there is a way I can force the metadata library allocation (pre-assigned) to occur before either the autoexec or an initstmt, please let me know. ... View more