topic Re: Including a stored process from a stored process on the metadata server in SAS Procedures

Including a stored process from a stored process on the metadata server

wolowicz — Mon, 20 Jun 2022 20:10:08 GMT

Hi there,

I have a series of business case testing that are done across 12-20 stored processes. Each of these have the same data gathering logic (with some filter variables, and different inputs), and then a section with each of the specific tests. I would like to take the data gathering logic out and move it to a stored process that is called by each test so that I only need to update it in one place.

Base-Data

The Data gathering step runs a series of ProcSQL steps creating the base datasets for the tests. It does not output anything to a report or screen.

Test processes

The tests run simple procSQL tests, and output ODS Excel objects (we run these from Excel using the office plugin).

Here is an example test:

/*Classes to Check*/  
%let Classes = %NRSTR("PS","PT","PH");  

/**************************************************************************************  
Include the Base views that all of the tests use  
***************************************************************************************/  
%include "./AppointmentAuditBaseViews.sas" /source2;  
 
/*######################## 
Check to see if the right dues are setup 
###########################*/ 
proc sql; 
options compress=yes; 
create table DuesNotSetup as 
SELECT * FROM WORK.JobsWithDeductions WHERE 
AllDeductions not like "%DPE%" ; 
run; 

proc sql; insert into ReportSummary SET Title="Dues not Setup", Results =(select count(D.CKey) from DuesNotSetup D); quit; 
ods excel; 
title j=left "Dues not Setup"; 
footnote2 j=center color=blue 
height=10pt "&sysdate9"; 
proc report style= [just=left] data=WORK.DuesNotSetup; 
run; 
ods excel close; 
/*Summary report to count how many tests are left*/  
ods excel;  
title j=left "Summary";  
footnote2 j=center color=blue  
height=10pt "&sysdate9";  
proc report style= [just=left] data=WORK.ReportSummary;   
run;   
ods excel close;   
  
quit;

The problem that I am having is that we store the process on the metadata server, and I cannot figure out how to include a program that is stored on the metadata server, and the system throws an error around the saving of the WORK file if I save it on the drive rather than in metadata.
Does anyone have any advice?

Re: Including a stored process from a stored process on the metadata server

Quentin — Mon, 20 Jun 2022 22:15:00 GMT

Your stored process that is stored on the metadata server can still %include a .sas file that is stored on the OS storage of your SAS server. You might need to work with your admin or a colleague to understand which SAS server your stored process is running on, and where you can store .sas files to be %included by the server.

If you have EG or Studio, it might be easier to use those and see what file storage locations you can see.

All of my stored processes are stored in metadata, but the source code is typically just a %include statement that points to a .sas file sitting on our server. So the STP is just a wrapper. My DI Studio jobs are the same thing, just a single node which %includes a .sas file.

Re: Including a stored process from a stored process on the metadata server

andreas_lds — Tue, 21 Jun 2022 05:35:06 GMT

Could be done by adding a parameter to the process gathering the data that allows selecting the process to be run afterwards. BUT this is far more difficult than using %include: you have to edit the list of process manually, you can't use the default macros %stpbeginn and %stpend, instead you have to open an ods destination and write the html-code that calls the next process. I am not sure that this will work at all, if the process to be executed has no parameters. Here is an excerpt of the code i wrote some years ago:

      %local url programmName;
      %let programName = %sysfunc(urlencode(&TargetProcess));
      %let url = %str(https://&_SRVNAME/SASStoredProcess/do);
      %let url = %str(&url?_action=properties,nobanner%nrstr(&_program=)&programName);

      data _null_;
         file _webout;         
         put '<HTML><HEAD>';
         put '<META HTTP-EQUIV="Refresh" NAME="NULL"';
         put "CONTENT=""0;URL=&url"">";
         put '</HEAD>';
         put '<BODY>';
         put '</BODY>';
         put '</HTML>';
      run;

TargetProcess has the full name (including metadata-path) of the process.

Re: Including a stored process from a stored process on the metadata server

Quentin — Tue, 21 Jun 2022 11:23:50 GMT

When you say:

I would like to take the data gathering logic out and move it to a stored process that is called by each test so that I only need to update it in one place.

That sounds to me like the logic (code) should be in a macro, not necessarily a stored process.

It is straight forward to call a macro from a stored process definition. It's more difficult to literally call a stored process from a stored process (I have done it, by having a stored process generate an html frame that displays the URL of the called stored process).

Re: Including a stored process from a stored process on the metadata server

wolowicz — Tue, 21 Jun 2022 16:56:37 GMT

My macro skills are very very basic. Can you save the Macros to the server as a library or something other than a stored process?

Re: Including a stored process from a stored process on the metadata server

wolowicz — Tue, 21 Jun 2022 17:05:23 GMT

Hi Quentin,

It's good to know that you are not having issues.

When I do the include it seems to be grumpy with a work directory. Maybe its a permissions or file path issue:

ERROR: The result package could not be created. The error encountered was:
ERROR: No files were added to the package because the STP work directory
(/**/SASWORK/SAS_work54AC000024E2_***********************/SAS_work2D2D000024E2_***********************/2d3f7ee5-b21d-3342-b441-9fa25df3ee70/) is empty.

I have stared out the sensitive stuff.

If I change it to stream results, I get an error with characters before starting the output XML.

Re: Including a stored process from a stored process on the metadata server

Quentin — Tue, 21 Jun 2022 17:22:42 GMT

The easiest way to to just save the macro as a .sas file sitting somewhere on the server's file system. But you don't need to use a macro, you could also just have a .sas file that you %include. The .sas file would not have all the stored process wrapper stuff (%sptbegin, %stpend, etc.), it would be just the DATA steps or SQL steps you need for the data munging you want to do.

Re: Including a stored process from a stored process on the metadata server

Quentin — Tue, 21 Jun 2022 17:36:40 GMT

I always use streaming results.

For having a stored process that called a child stored process, I had the parent stored process generate a frame, and the URL displayed in the frame was the child stored process.

So the parent stored process is writing an HTML file to return the the browser. And then at some point it uses PROC ODS TEXT to insert the HTML for the frame to display the child stored process.

Something like:

      %local frameHTML;
      %let frameHTML=<iframe src="" https://myserver.com:443/SASStoredProcess/do?%nrstr(&_program=)&_metafolder.SomeStoredProcess"" 
                     height="" 100% "" 
                     width="" 100% "" 
                     frameborder="" 0 "" noresize scrolling="" auto "">Browser must support frames!
                     </iframe>;

      proc odstext;
        p "&frameHTML";
      run;

This was a long time ago, and I'm sure there are MUCH better ways. : )

Re: Including a stored process from a stored process on the metadata server

wolowicz — Tue, 21 Jun 2022 17:40:05 GMT

My processes output to Excel directly with ProcReport, so I can't (shouldn't) put HTML and frames in.

Re: Including a stored process from a stored process on the metadata server

wolowicz — Tue, 21 Jun 2022 18:01:37 GMT

This worked. I saved it as a file to the server (not as a stored process, but as a "save-as"), then referenced it in the code with an %include. My issues with input and output buffer control all went away.

Re: Including a stored process from a stored process on the metadata server

AllanBowe — Wed, 22 Jun 2022 11:52:21 GMT

I see you're running tests - have you considered using sasjs test? This approach pushes all your tests as stored processes, executing them from a local CLI. The approach will also work on Viya, and Base SAS, simply by updating your connection details.

Back to your question - normally you can include your stored process using `proc stp`. However, if you are just interested in grabbing the source code, you can also use this macro: https://core.sasjs.io/mm__getstpcode_8sas.html

If you are looking for an environment-agnostic macro, there is also this one: https://core.sasjs.io/mx__getcode_8sas.html

The test for the above is here! https://core.sasjs.io/mx__getcode_8sas.html