Thanks Nick and Tom, I have finally got it working, although now seem to be unable to flag this up as answered??? For future reference and for anyone who hits the same problem here is what I did to get a cascading prompt in enterprise guide that relies on macros elsewhere in the project and returns data to the current session... I took all of the code from my enterprise guide autoexec flow that did not rely on prompt values and saved it onto my server. I then replaced the code with a new program that had a %include to the saved code to test that it all worked fine. I created new macros that create a data set (if it doesn't already exist) in the user's Home directory on the server and then save the value of a macro variable into the table with an ID column that I can use later to identify the project in case I need the same trick elsewhere. Code for these macros at the end... Then in the autoexec code in my project I use these macros to save any macro variables I want to be available to a stored process. The most important one being %Save_MV(MyWork, %sysfunc(getoption(work)), ProjectID); which saves the full path of the current work directory. Then before converting my standard EG program into a stored process I add some lines at the start to %include all the same code as in the autoexec and use %Restore_MV to re-define any macro variables I need for the process. (Remembering to use %global to define them in the global symbol table first!). I then take the value of &MyWork to create a libname statement that points to the work area for my user session and update the code to output any final tables to this directory. I created the stored process to run on the WORKSPACE server rather than a stored process server, this means that the new session that the stored process uses is created with the same credentials as the user who triggered it so I have no permissions issues connecting to the home area or writing to the other session's work area. I can then create the dynamic cascading prompts that I want and have the output returned to the work area of my current session. some example code: /* Program 1 - this defines the macros i need and tests saving a macro variable */ %macro Save_MV(MVName, MVValue, MVSysID); libname MyHome base "/home/&SYSUSERID"; %if not %sysfunc(exist(MyHome.SPMacro,DATA)) %then %do; data MyHome.SPMacro; Length SysID $32 Name $32 Value $200; run; %end; data tmp_Record; Length SysID $32 Name $32 Value $200; SysID="&MVSysID"; Name="&MVName"; Value="&MVValue"; run; proc sql noprint; delete from MyHome.SPMacro where strip(name)="&MVName" and strip(SysID)="&MVSysID"; quit; proc append base=MyHome.SPMacro data=tmp_Record; run; proc datasets lib=work memtype=data force nolist nowarn; delete tmp_Record; run; Libname MyHome clear; %mend; %macro Restore_MV(MVName, MVSysID); libname MyHome base "/home/&SYSUSERID"; %if not %sysfunc(exist(MyHome.SPMacro,DATA)) %then %do; %Put ERROR-No Macro variables have been saved for the current user; %end; proc sql noprint; select strip(value) into :&MVName from MyHome.SPMacro where strip(name)="&MVName" and strip(SysID)="&MVSysID"; quit; Libname MyHome clear; %mend; %Save_MV(TestMV, TESTING, MyProject); %Save_MV(MyWork, %sysfunc(getoption(work)), MyProject); /* Program 2 - this gets turned into a stored process */ %global TestMV MyWork; %Restore_MV(TestMV, MyProject); %Restore_MV(MyWork, MyProject); libname MyWork base "&MyWork"; data mywork.TestOutput; MV_Value="&TestMV"; run; Libname MyWork clear;
... View more
