BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
wolowicz
Fluorite | Level 6

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
PROC Star

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. 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.

View solution in original post

10 REPLIES 10
Quentin
PROC Star

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.

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
wolowicz
Fluorite | Level 6

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. 

Quentin
PROC Star

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. : )

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
wolowicz
Fluorite | Level 6

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

andreas_lds
PROC Star

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.

Quentin
PROC Star

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).

 

 

 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
wolowicz
Fluorite | Level 6

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? 

Quentin
PROC Star

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. 

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
wolowicz
Fluorite | Level 6

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. 

AllanBowe
Barite | Level 11

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

 

 

 

 

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 500 views
  • 1 like
  • 4 in conversation