Your SAS programs, embedded in web apps and elsewhere

Submitting a long running job in batch mode from a stored process

Reply
Super Contributor
Posts: 376

Submitting a long running job in batch mode from a stored process

Hi,

We submit our stored processes via the Stored Process Web Application.  My particular stored process is using prompts to provide a user interface to the user.  However, since the actual processing can take a long time (30-60 minutes) to run, the "heavy lifting" is done by a SAS job running in batch.  The actual results will be emailed back to the user as a PDF attachment.

Since the code isn't that long, I've included it below.  What I'd like to know is if this is best practice, or if there is a better approach?  In particular, the documentation says:

_ACTION

Used By

Web Clients

Description

Specifies an action for the Web application to take. Possible values for this variable are as follows:

BACKGROUND
executes the stored process in the background.

Can someone explain what this means exactly?  How is "background" different than "not background"?  The documentation doesn't make this clear.

Here's the code:

Stored Process (the prompts are dynamic lists based on SASHELP.SHOES for Region, Subsidiary, and Product):

%STPBEGIN;

OPTIONS VALIDVARNAME=ANY;

*  End EG generated code (do not edit this line);

/*

Set all necessary macro variables.

If they are macro variables that you want sent to the batch job,

best practice is to create them all with the same prefix.

I am using the prefix "batch_".

Otherwise you need to change the PROC SQL code below.

*/

* macro variables sent to the batch job ;

%let batch_username = &_username;

%let batch_jobname  = Test SAS Batch Job;

/*

Create a Stored Process (STP) session, which allocates the temporary SAVE library.

This library is used to pass parameters between the STP and the batch job.

While we are creating the STP session, also create the confirmation message.

Note that the "email when the job finishes" must be generated by the batch job,

not this Stored Process.

*/

data message;

  * session timeout is in seconds - 2 minutes should be plenty ;

  if _n_ = 1 then do;

    if libref('SAVE') ne 0 then do;

      rc = stpsrv_session('create',2*60);

    end;

  end;

  infile datalines truncover;

  input message $200.;

  keep message;

  datalines4;

Job &batch_jobname submitted at %sysfunc(strip(%sysfunc(datetime(),datetime18.))).

You will receive an email when the job finishes.

;;;;

run;

libname save list;

/*

This macro variable must be set AFTER the session is created.

*/

%let batch_save_library=%sysfunc(pathname(save));

/*

Now copy the desired parameters (macro variables) to the SAVE library.

This dataset will be used by the batch job to recreate the macro variables.

*/

proc sql noprint;

  create table save.parameters as

    select

      name

      ,value

    from

      dictionary.macros

    where

      name like 'BATCH_%'

  ;

quit;

/*

Setup the parameters for invoking SAS, then launch the SAS job.

You MUST pass in the path to the SAVE library location via sysparm.

My preference is to use a configuration file, rather than command line options,

with ALL other job configuration (eg. -sysin) done in the configuration file.

This gives the maintainer of the code ONE place to go to change the batch job options.

However, you can configure this however you wish.

*/

%let sas=C:\SAS\Config\Lev1\SASApp\sas.bat;

%let config=&batch_config_file;  /* if you have created a hidden STP parameter,

                                    otherwise hard code this here */

%let sysparm=&batch_save_library;

%let options=;

* invoke the SAS batch job ;

systask command "&sas -config &config -sysparm ""&sysparm"" &options" nowait;

/*

Now display the confirmation message to the user.

The options nomerror and noserror suppress warning messages in the log

for unresolved macro and unresolved macro variable references.

HTML code in particular can have tokens (eg. &program=...) that look like macro variables

but are HTML tokens instead.

*/

%let options=%sysfunc(getoption(merror,keyword)) %sysfunc(getoption(serror,keyword));

options nomerror noserror;

data message;

  set message;

  message=resolve(message);

run;

options &options;

title;

options nonumber nodate;

proc report data=message nowd noheader center ls=96 spacing=0;

  columns message;

  define  message / width=96 " ";

run;

*  Begin EG generated code (do not edit this line);

;*';*";*/;quit;

%STPEND;

*  End EG generated code (do not edit this line);

And here's a sample SAS batch job:

/*

Allocate the SAVE library passed in via sysparm

*/

libname save "%scan(&sysparm,1,%str(^|))";


/*

Set macro variables passed in to the batch job by the Stored Process

*/

data _null_;

   set save.parameters;

   call symputx(name,value,"G");

run;


/*

The rest of your program goes here...

You would process the macro variables just like you would in a stored process.

*/


* Print macro variables ;

options nocenter;

proc sql;

  select

    name,value

  from

    dictionary.macros

  where

    name like 'BATCH_%' and scope="GLOBAL"

  order by

    name

  ;

quit;


* Print SASHELP.SHOES filtered on input parameters ;

* Note:  %stp_seplist processes the STP parms, creating a separated list ;

options mprint;

%macro stp;

  %local where;

  %let where=%sysfunc(sum(&batch_region_count,&batch_product_count,&batch_subsidiary_count));

  options noserror nomerror;

  data subset;

    set sashelp.shoes;

    %if (&where) %then %do;

      where 1

    %end;

    %if (&batch_region_count) %then %do;

      and region in (%stp_seplist(batch_region))

    %end;

    %if (&batch_product_count) %then %do;

      and product in (%stp_seplist(batch_product))

    %end;

    %if (&batch_subsidiary_count) %then %do;

      and subsidiary in (%stp_seplist(batch_subsidiary))

    %end;

    %if (&where) %then %do;

      ;

    %end;

  run;

  options serror merror;

  proc print;

  run;

%mend;

%stp


/*

Send email that the job has finished.

*/


* Create message dataset ;

data mail;

   length parm $15 line $1000;

   infile datalines dsd dlm="|" truncover;

   input parm line;

   datalines4;

| Your SAS job &batch_jobname has completed with return code rc=&sysrc..

;;;;

run;

%sendmail(to=&batch_username,subject=%str(Job &batch_jobname has completed),content_type=text/html)

SAS Employee
Posts: 7

Re: Submitting a long running job in batch mode from a stored process

Hey Scott,

Did you have a look at Usage Note 39114: Tips for running a SAS® Stored Process in Background mode using SAS® 9.2 ? The Note explains what running in "background" means.

Kind regards,

Bago

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

Thanks Bago!  I'm experienced in SAS programming but less so with Stored Processes.  All Usage Notes, SGF's papers, useful links, etc. are more than welcome!!!

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

I've reviewed the Usage Note above.  Note that we don't have the complete EBI installation.  So, we don't have the Information Delivery Portal, Web Report Studio, or Information Map Studio.  We do have the Stored Process Web Application and WebDAV (although we haven't been using WebDAV at all). 

This URL does work though: 

http://myserver.com:8080/SASContentServer/repository/default/sasdav/Users/sbass/

although there is no content ;-)

Occasional Contributor
Posts: 18

Re: Submitting a long running job in batch mode from a stored process

Scott:

When the code runs in the background - it allows the user to break away from the Web Browser session.  Else if you started running the stored process and closed the browser -- then your STP stops.  Long running STPs obviously creates a great chance that the user will close the browser without realizing what he/she did.

I'm blogging about STP topics at Business Intelligience Notes for SAS BI Users and have co-authored a one-spot reference book about learning Stored Processes that discussing topics such as this one.

Tricia

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

Hi,

I'm happy to buy the book, as long as it's appropriate to my environment - no IDP, WRS, IMS, AMO, etc.  SPWA and WebDAV (Jack Rabbit) only.

Thanks,

Scott

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

Hi,

I've thought about this a bit more, and wanted to know if the details of background processing are documented anywhere?  For example, does it spawn a new process under the sassrv credentials?  Can I change the text returned to the user when I launch a background STP?

Also, if I launch a batch process myself as documented above, and close my browser, will it kill the batch session (I suppose I could just try this...)

Thanks,

Scott

PROC Star
Posts: 1,236

Re: Submitting a long running job in batch mode from a stored process

Hi Scott,

I too am in the process of learning about stored processes after years of writing SAS code.  So I'm going to respond to your question with more questions, and a few suggestions.

My main question is why you chose to set off a batch job?  Perhaps only because the job runs a long time? (And agree, you might be better off looking into other background processing options).

I hadn't seen people using stored procedures to kick off batch jobs, so curious to what others think of this approach?  On the one hand, it's a tempting approach to me.  Because as a newbie to stored processes, I would once again feel confident that I could control things like autoexec, config.sas, etc., without worrying about how these are set by an administrator.  I would also get a nice log file back, without needing to use proc printto or looking for server logs.

But on the other hand, relying on kicking off a new SAS session feels a little bit like running home to what feels comfortable as a SAS programmer.  Rather than pushing for new approaches as a SAS developer.  And I can see down side to the approach, e.g. time to start up a new session, and more importantly by-passing of meta data security in the the new session (cuz it knows not of the meta data, right?).  Will look forward to seeing what those with more experience have to say.

For resources I've found handy, I would have found Both of Tricia Aanderud and Angela Hall's books helpful  (Building BI Using SAS and 50 Keys to Learning SAS Stored Processes).  If you have to choose one, I would get the Stored Process book since you don't have the full EBI setup.  It's great. Works through nice examples.  I see Tricia already recommended her Blog.  I've also found Don Henderson's blog to be a great resource: http://hcsbi.blogspot.com.  I just ordered Don's book, Building Web Application with SAS/Intrnet.  While it's a few years old, a friend of mind recommended it, and much of the content/logic/process is the same whether you are using the new BI tools / stored procedures or writing your own code.  And from seeing Don's blog, and SAS commuity postings, and responses to my questions here in the forums, I'm sure it won't disappoint.

HTH,

--Q.

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

Hi Quentin,

I'm used to this model in other web based products.  The main one that comes to mind is PeopleSoft.  The web interface allows you to select a task, set some options, and submit it asynchronously.  You can then use another web interface to monitor the status of running tasks.

The builtin background processing looks promising, now that I have a better understanding of what it does.  The SAS documentation is a bit "sparse" in this regard.  But hey, that's what usage notes are for, to fill in all the holes in the doc ;-)  Anyway, no sense reinventing the wheel if it's already perfectly round.  But I would love to know the details between the builtin background processing vs. my approach as documented above.

The batch SAS job is started with metaautoresource "SASApp", so it will have the same libname settings as an EG workspace server session.  AFAIK, it will honor metadata security, although this isn't really a concern in my scenario.

I'll probably order the 50 Keys to Learning SAS Stored Processes book once Tricia gets back to me and confirms that it's suitable to my environment. 

Regards,

Scott

Occasional Contributor
Posts: 18

Re: Submitting a long running job in batch mode from a stored process

Scott:

Here's a link to the site where you can download the table of contents:

http://www.bi-notes.com/the-50-keys-to-learning-sas-stored-processes/

Two of the chapters discuss using stored processes with the BI tools and the web.  The other information would be useful to learning how to write stored processes and giving you some ideas of the many ways these can be used.

Tricia

N/A
Posts: 1

Re: Submitting a long running job in batch mode from a stored process

This background processing method is very useful for me in an web application that I'm building as previously my browser wasn't showing it had finished processing.

Whilst the adding the _action=background to the URL to get the stored process working in the background, how can I know if it has error?

I'm rather dubious of setting something up without a method of being able to flag errors.

There seems to be a mention of the 'Stored Process Alerts Portlet' but when I artificially trigger an error, nothing is flagged in it.

In addition, as I currently open a new page through a link to call the stored process (e.g. target=_blank), is there a way of calling the stored process and then closing the window?

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

Hi,

Since my original post, I've progressed this project to its conclusion, including a couple SAS TS tracks, and can provide more comments.  These comments also address some of Quentin's questions above.

I did continue with my approach, i.e. STP submitting a secondary SAS batch job, rather than reworking the STP for background processing.  Some of the reasons:

1) I have more control over the spawned SAS session:  output log location (rather than having the very long SAS log buried within the STP log), memory allocation for the batch SAS process, I can run it under another metadata userid, etc.

2) I have more control over the message displayed to the user.  I wanted more than just "Your job <whatever> has been submitted for background processing".  In particular, I wanted to let me know to expect an email with an attachment.  If SAS made it easier to customize the message returned from a background STP, on a per-STP basis, I would have considered the builtin background processing more strongly.

3) If something was completely haywire with the job, it's easier to kill the process via Process Explorer, rather than bringing down the entire STP server (I have RDP access to the remote SAS server).

I learned (via SAS TS) that, when the STP server starts, an authentication token gets created to authenticate to the metadata server.  However, when the STP server launched the SAS batch process (with the metaautoresource option), this authentication token for the metadata server was expired.  I was getting an invalid credentials message in the SAS batch session, and none of my pre-assigned libraries were being allocated.  The solution is to invoke the SAS batch process with the -metauser and -metapass options.

I had a few issues with quoting in building the SAS invocation command, esp. quoting such that macro parameters would be expanded.  See the attached stp_batch_submit macro.

My STPs had a hidden parameter batch_config_file with the path to a SAS config file, which contained all the invocation options for the batch job.  The STPs were streaming, not package, since I'm displaying a confirmation message back to the user.

The STP code itself is quite simple:

%* resolve embedded %sysget function call ;

%let batch_config_file=%unquote(&batch_config_file);

%stp_batch_submit(

config=&batch_config_file

,mvars=name like 'BATCH_%' or name like '_ODS%'

/* ,messagefile=C:\SAS\Config\%sysget(lev)\SASApp\SASEnvironment\SASCode\Saspgms\sample_stp.html */

);

Of course, all the heavy lifting is done in the %stp_batch_submit macro.

@sasgeek, regarding your question "...is there a way of calling the stored process and then closing the window", you can do so with javascript.  In my code, I'm displaying a javascript alert, then closing the window.  I wouldn't want to close the window immediately, or the user wouldn't see the message!  See the stp_batch_submit macro for example code.  Note:  the javascript code has slightly different characteristics depending on browser (IE, Chrome, Firefox).

Finally, I wrote some macros to help with this process, which I've attached to this post.  Wherever you see "parmv", substitute your own macro parameter validation code.

HTH,

Scott

Attachment
Attachment
Attachment
Attachment
PROC Star
Posts: 1,236

Re: Submitting a long running job in batch mode from a stored process

Thanks for posting, Scott!  This will be fun weekend reading.  You should consider developing it into a user-group paper.

A couple questions (that might be obvious once I dive in).

1. If the stored process in running on stored process server (as SASSRV), I assume the spawned session also runs as SASSRV?  Is there a Unix- way to spawn a SAS session and have it run as somebody else?  I would love to have the sesion run as the actual user, rather than SASSRV.  (I know you can specify a different metadata identify for the spawned session).

2. So when you spawn the session, I see where you are specifying the metadata identity in stp_batch_submit.sas:

%* We need to specify the user credentials for the spawned SAS job :
%let parms=&parms -metauser someuser -metapass {sas002}somepassword;

/*Invoke the SAS batch job.*/
systask command " ""&sas"" &parms " nowait;

Are you hard coding -metauser and  (encoded) -metapass?

Or if you want to just pass these along from whoever called the stored process, can you do that somehow? (I see SPWA creates &_METAUSER but obviously not &_METAPASS)

Obviously users could enter a password in a prompt, but ot sure you would really want to have users enter their password into a stored process prompt, where it could be easily revealed...

Good stuff, thanks for sharing your results!

-Q.

Super Contributor
Posts: 376

Re: Submitting a long running job in batch mode from a stored process

Hi Quentin,

1. If the stored process is running on stored process server (as SASSRV), I assume the spawned session also runs as SASSRV?  Is there a Unix- way to spawn a SAS session and have it run as somebody else?  I would love to have the sesion run as the actual user, rather than SASSRV.  (I know you can specify a different metadata identify for the spawned session).

Yes the spawned session runs as sassrv, but I changed the metadata userid to be our production user so the STP would have metadata access to all required libraries.  This was fine for my needs.

For the Unix question, see the "su" and "sudo" commands.  However, you'd still need a password to switch to another user, unless you were running the su command as root.  Running the STP process as root (or the equivalent) would open all sorts of security issues and I'm not recommending it.  I would have a chat with your Unix sysadmin / security person if you want to do this. 

However, if you're on SAS 9.3, IIRC SAS 9.3 has added streaming output to the workspace server.  So, you could have your STP run from a workspace server.  You'd have a slight performance hit as the workspace server started, but it would run under the userid who logged in to the SPWA.  I think this is correct; I recall reading this in a "What's new in SAS 9.3" document.  If this does work as I think it would (I'm still on SAS 9.2 so can't test it), that's what I would do if it were critical that the STP run under the credentials of the logged in user.

2. So when you spawn the session, I see where you are specifying the metadata identity in stp_batch_submit.sas:

%* We need to specify the user credentials for the spawned SAS job :
%let parms=&parms -metauser someuser -metapass {sas002}somepassword;

/*Invoke the SAS batch job.*/
systask command " ""&sas"" &parms " nowait;

Are you hard coding -metauser and  (encoded) -metapass?

Or if you want to just pass these along from whoever called the stored process, can you do that somehow? (I see SPWA creates &_METAUSER but obviously not &_METAPASS)

Obviously users could enter a password in a prompt, but ot sure you would really want to have users enter their password into a stored process prompt, where it could be easily revealed...

I hardcoded the metauser and metapass in the macro, but I didn't have to do so.  In my environment, this does not pose a security risk.  However, if it did pose a risk, I would move these settings to the SAS configuration file, which is created by the developer of the STP (which in my case is me).  Or, the caller of the macro could add them as options.  In either case, the STP author would need to know the password of the specified metadata identity.

See http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/input.html (scroll to the bottom) for information on securing sensitive information such as passwords.  So, other than the hassle of requiring the user to enter his/her password in a prompt (say to feed to the sudo command), you should be able to do this in SAS in a secure manner.

HTH,

Scott

PROC Star
Posts: 1,236

Re: Submitting a long running job in batch mode from a stored process

Thanks Scott,

Yah, the ability to have workspace server send streaming results is what has me most excited about 9.3.  It will solve a lot of my security concerns.  The password masking stuff is neat (thanks for the link), but I think in my setting, any little ad-hoc app that collected passwords might raise eyebrows.  Hopefully we'll get 9.3 set up soon.

--Q.

Ask a Question
Discussion stats
  • 19 replies
  • 3698 views
  • 0 likes
  • 6 in conversation