Looking to Set Up SAS Web Application Stored Process as a RESTful API for non-SAS Users

[LukeW21]

As a disclaimer, I am new to SAS Web Applications, Stored Processes, the ODS function, and APIs as a whole, with only introductory knowledge on the subjects as I've only just finished putting together my first web application stored process. I am aware SAS Viya has functionality for building APIs, but I do not have access to SAS Viya at my company, and it is not going to be an option before this project is needed. With that said, I am looking to get the output of a web application stored process I recently created and have it easily downloaded to whatever folder the end user designates.

Presently, I query some SAS Metadata and use the ODS function to load it into a temporary excel file:

%macro excel_output;

 filename exceltmp temp; 
 %let excelpath=%sysfunc(pathname(exceltmp)); 
 %put &=excelpath;

 ODS EXCEL file="&excelpath..xlsx" style=statistical 
 options(sheet_name="iam_sas_metadata"); 
 PROC PRINT data=sas_users noobs; 
 RUN; QUIT; 
 
 ODS EXCEL CLOSE; 

%mend excel_output;

This now creates / displays the excel spreadsheet (I enabled both the "Stream" and "Package" options when creating the stored process). But the issue is in downloading this file by means of calling the application / process like you would an API. I've tried PROC HTTP in order to replicate the API GET call to retrieve the data, although when I provided login credentials using the webusername and webpassword optional arguments, it would still only return the Logon Manager page as an html file, evidently not using the credentials I attempted to pass through to get by this page.

I also tried curl commands, though those either ended up failing or hanging without any log I was able to retrieve, and I tried using both verbose and piping the output of the command to a log. 

PROC STP does not seem like an option either as the end user is looking to be able to call the web page like a RESTful API from their end using a non-SAS program and have the excel file downloaded to their system. Does anybody know how I can set things up to make that doable, or how I might be calling the program incorrectly in order to get this to happen? Thank you in advance and please let me know if I can provide any other important details.

[ChrisHemedinger · Accepted solution]

This paper might help:

https://support.sas.com/resources/papers/proceedings20/4243-2020.pdf

Main things to note:

- Use the special fileref _WEBOUT to direct your output to what the browser can download for the user.

- Use the stpsrv_header function to define the content type and name for the expected download. Ex:

data _null_;
  rc = stpsrv_header('Content-type','application/vnd.ms-excel');
  rc = stpsrv_header('Content-disposition','attachment; filename=output.xlsx');
run;

[LukeW21]

Thank you for this information, one main thing to note for anybody coming across this post in the future is I had to modify the stored process itself in the management tool to disable the option to include code for stored process macros, I believe the stored process was defaulting to HTML and ignoring the headers setting the content to .xlsx format.

Additionally for anybody who might be coming across this, the formatting for the URL that I was struggling with was &_username=[Username]&_password=[Encrypted Password] at the end, I was struggling with finding the formatting for a short time. 

Thanks!

[AhmedAl_Attar]

@LukeW21 

Here is an additional resource to look into SAS® 9.4 Stored Processes: Developer’s Guide, Third Edition 

Try to access the SAS Stored Process Web Application http://yourserver.com:8080/SASStoredProcess/do , within this web app, there are a list of Stored Process Samples, one of these samples, illustrates how to use multiple output formats "Sample: Multiple Output Formats - Using ODS to generate PDF, PostScript, RTF and other output" 
You can use this sample as a guide.

Hope this helps