Hi,   Further to SASJedi's reply...   After my initial post, I opened a track with SAS TS with the same question.  Of course, their response was the same as SASJedi's, although they did comment that it sounded like a good enhancement request.   On a more global level (i.e. not just restricted to DS2), I wish that SAS supported the concept of a global hash object:  a hash object that would persist beyond the current data step.   In my previous job, we had a number of very large nightly ETL jobs.  Many of the fact loads had to source the same dimension tables into hash objects in order to add the surrogate keys.  It could take 10-15 mins for each job just to load the same hash objects.  Wouldn't it be great if we could load the hash objects once, process the fact jobs, then dispose of the hash objects?   To that end, I also wish that SAS supported an "always on" server, analogous to many RDBMS's.  This could potentially support a hash object global to the entire server.  In that scenario, I would load the global hash object, run the ETL, then close the hash objects.  Garbage collection could be an issue - perhaps load the hash objects with a timeout value, where they delete themselves if not touched within the timeout period?   Of course, this is blue sky, and would require huge architectural changes to SAS.  Unless LASR supports this concept?   In my particular problem which triggered this post, there was no way I could load one hash for each DS2 thread - it would exceed the memory capacity of the entire server, let alone the SAS memsize option.  Had I pursued DS2 threading further, I would have investigated using index key lookup for each thread:   http://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#p00hxg3x8lwivcn1f0e9axziw57y.htm, example 7.     ... View more

Thanks @Patrickfor your reply.   I couldn't work out how to share/persist a connection on SQL Server when using explicit pass-through, which would be a requirement for my application.  I learned a bit more about SQL Server temporary tables and their support in SAS this thread, but I'm not quite there yet.   Sample code:   proc sql; connect using tmp; execute ( select * into ##systables from tempdb.sys.tables; ) by tmp; select * from connection to tmp (select * from ##systables); quit; proc sql; connect using tmp; select * from connection to tmp (select * from ##systables); quit; If anyone knows how to get the 2nd query to work, please let me know.  AFAIK, I need a way to persist the first connection, and have the 2nd query re-use that same connection.   Bonus points if you can get this to work with #systables instead of ##systables.   Oh yeah...SAS 9.3 on Windows Server. ... View more

Ok, I think I can answer my own question.   I found this link, which was helpful: http://www.sascommunity.org/planet/blog/category/temp-table/   So I then created this test code (don't worry about the macro - it simply allocates the SQL Server libref via ODBC):     %libname_sqlsvr(libref=TMP,database=MyDatabase,options=schema=dbo connection=shared bcp=yes) data tmp.'##class'n; set sashelp.class; run; proc sql; connect using tmp; execute( select * into ##systables from tempdb.sys.tables ) by tmp; select * from connection to tmp( select * from ##systables ); quit; data class; set tmp.'##class'n; run; data systables; set tmp.'##systables'n; run; proc sql ; connect using tmp; create table systables as select * from connection to tmp ( select * from ##systables ); disconnect from tmp; quit; proc sql ; connect using tmp; create table class as select * from connection to tmp ( select * from ##class ); disconnect from tmp; quit;     Things worked as expected/desired for #class, but not for #systables.  Even though the first SQL query against ##systables showed that ##systables exists (for a brief moment in time), I could never access it in a subsequent step.   I also did a search and replace of ## with #, then re-ran the process.  What was interesting is that, even though I never could see #class in tempdb via SSMS (because it is a different connection), I could access it from my SAS program (because the libref is keeping the connection open).  This is useful, as my temporary files could contain data that, while not super-sensitive, I'd prefer others cannot see via SSMS.  However, in this scenario, the final proc sql step failed; I can access #class via the libref, but not via explicit pass-through.   I tested with both connection=shared and connection=global with the same results.   Then the light bulb lit:  explicit pass-through isn't going through the SAS library, so any temporary tables created via explict pass-through won't be available after the connection (proc sql step) ends.  This also means that any library options, such as connection=shared, don't apply.     I had SSMS open, viewing tempdb.Temporary Tables, refreshing after each SAS step.  I could see ##class, but never could see either ##systables, #systables, or #class.   Let me know if this analysis is flawed.  Sorry for the false alarm.  I hope this may help someone who finds this via search someday. ... View more

One more thing, along the same lines as this thread...   I have a long running program entry in EG.  That entry is running PROC SQL with explicit pass through.   I want to queue up the next program entry in the process flow, so it will get queued and launch immediately after the first one ends.     I select the 2nd entry, RMB, Run...I get the message "Connecting to server SASApp", get a busy cursor, and EG becomes unresponsive, i.e. clicking on the taskbar icon won't even raise the window.   Eventually the 2nd program runs, but the temptation is to think that EG is hung and kill it.  And of course this prevents me from then queuing the 3rd program in the list.   EG 6.1 M1 ... View more

Hi All,   Thanks for the replies so far.  To wrap up this thread...   Sorry, I think mentioning an external database was a red herring and opened a can of worms.  I only mentioned it since it was the cause of the long running job.   Here is my code, edited slightly for privacy:     * fact table ; data workspde.main_fact_table; set odbclib.vw_record; where hospital_type eq 2 and record_status ne 4 ; run; * lookup tables - this took about 2 hours ; * there is a reason I wasn't using pass through ; proc copy in=odbclib out=workspde; select lookup1 lookup2; run; * join ; proc sql _method; create table workspde.final as select * from workspde.main_fact_table a left join workspde.lookup1 b on a.key1=b.key1 left join workspde.lookup2 c on a.key2=c.key2 ; quit;     About 2 mins into the join, I clicked stop.  I think EG stopped about 10 mins later; I don't know for sure as I was in other windows at the time.   So at the time I clicked stop I wasn't running a query against an external database.  I was using a SPDE library if that matters?   Like I said, I think EG can only work with what SAS Foundation provides.  I don't have DMS available to test whether interrupting a similar join would immediately stop or not.   What I can say is that, speaking objectively and without bias (I love SAS software!) is that, for this situation, my user experience is better when I undertake a similar action in SSMS or Oracle SQL Developer.  Clicking stop immediately stops the query.   I'll see if Chris' suggestions help. ... View more

The 2 hours work was:   proc copy in=odbc_lib out=work;    select my_two_tables; run;   The SQL join was then against 3 work datasets.   I suspect EG can only work with what SAS Foundation gives it.  I just wish SAS would update the interrupt processing in the workspace server so its products work better.   I'd love for this suspicion to be incorrect, and there is a reliable way to get EG to stop when I click stop.   I don't have SAS server admin access.  Hopefully my SAS Admin runs some utility on a nightly basis to kill the zombie SAS workspace server processes, or else they otherwise die of their own accord since the connection to EG is dropped when I (usually but not in this case) nuke it via Task Manager. ... View more

I don't want to kill the process, otherwise I lose my 2 hours work.  I want EG to stop when I click stop.     Like most of other client-server products out there (ok, my sample size is only SQL Server Management Studio and the Oracle client, both of which allow me to stop a query) ... View more

Hi Andrew, the SAS Workspace Session is on a server somewhere ... View more

Hi,   Thanks for the replies so far.  Some additional context may be useful:   Due to my site's security requirements, the stored process must run under the workspace server, and under the logged in user's context. Therefore, the sassrv user has not been setup in the correct AD groups to access the source data anyway. While this doesn't preclude the use of the Stored Process Web Application Server, it does prevent a "streaming" stored process, which is a bummer. For these and other reasons, the stored process will be launched from EG.  The purpose of the stored process is to collect runtime parameters from the user via the prompt manager.  Once the prompts (macro variables) are set, the user will submit another EG process flow and/or program entry to run a long-running program in "batch".   I'd rather use the SPWAS to collect the prompts, then submit a proper batch job on the server, with proper logging, etc.  But ALLOWXCMD is not switched on the SPWAS.   I know I could just associate prompts with the long running job via EG, but I prefer the STP to collect the prompts.  I don't want the end user fiddling with all the backend error checking on the prompt values.   My "real" program could have say 20 prompts, plus a selection group to display different prompts based on the user's choice.  The workflow could be something like:   User sets the initial 20 prompts Next week the user changes say 5 of them, and does another run Next week the user changes say 3 of them, and does another run Next month the user changes all 20 prompts Rinse and repeat I have Use prompt value throughout project set for all the prompts.   One thing I have noticed is that the prompts persist across EG invocations; if I set prompts, close EG, open EG, and relaunch the STP, the prompts are as they were when the project was closed.  Although I lose the previous settings from the other selection groups, which is a bummer.   So, I'm guessing the state of the prompts is buried in the EG project somewhere, with no direct way for the end user (or programmer) to change them other than via the Prompt Manager UI.   The end users are fairly advanced, and will want to view the SAS log when the process ends.  They may also do minor tweaks to the base code of the program (which is readonly) before submitting the run.   I hope this context helps.  If you know of a better approach, please let me know.  (I will investigate whether the long running job can be a STP and I can use PROC STP to run the program "in batch"???).   Kind Regards, Scott ... View more

9.2 on Linux EG 4.3   Migrating to 9.4 and an upgraded version of EG (exact version unknown) in a month or so. ... View more

Hi,   RMB -> New Library Choose the pre-assigned library template Name=SASHELP, libref=SASHELP Assign to SASApp server Accept all other defaults   My current workaround is to create the library, but unassign it from the SASApp server unless I'm demoing the POC Stored Process.   But, again, I'm just not getting why it matters if the library is pre-assigned, and why this is considered a warning.  Or how to turn off this warning.  I just want to use some of the sample SASHELP datasets in both my prompts and stored process data sources. ... View more