Sas Web application Stored Procedure

[uguraltuntas67]

In web application STP i have parameter that refer to excel check box. İf this parameter is greater than 0 (it means excel chekckbox selected) i want to write 3 table in excel in 3 different sheet and user can download it from browser automatically. 

The current code below and it works but works for only 1 table. How can i make an excel output from 3 different tables? 

Any help would be appreciated.

Thanks

%global excel;

%if %length(&excel) ne 0 %then %do;

data _null_;

rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');

run;

ods excel file = _webout ;
ods excel style = Plateau;

proc print noobs data=work.table1; run;

ods excel close;

%end; /* in this do end block; i wanna make an excel file from work.table1 work.table2 and work.table3*/

%else %do;
%STPBEGIN;
proc print data=work.table1;run;

%STPEND;
%end;

[MarkBodt_NZ · Accepted solution]

I've written web based applications like this in the past that generate / download to the browser Excel workbooks.

I assume that when you say "Stored Procedure", you mean SAS Stored Process.

And I don't know if I'm over simplifying things, but I read your question is about how to write 3 tables to three different worksheets in excel.... and this doesn't really have anything to do with the stored process or web output that's wrapped around the Excel workbook creation?

So I've simplified your problem and come up with this simple code. This code creates three test datasets, then uses similar code to what you have. Rather than send this to _webout, I send this to a file on disk.

Run this test in EG:

* create 3 test data sets;
data one(where=(age=12))
two(where=(age=13))
three(where=(age=14));

set sashelp.class;
run;

*create workbook
ods excel file = "D:\excel.xlsx" ;
ods excel style = Plateau;
ods excel options(sheet_name= "12 year olds");

proc print noobs data=one;
run;

ods excel options(sheet_name= "13 year olds");

proc print noobs data=two;
run;

ods excel options(sheet_name= "14 year olds");
proc print noobs data=three;
run;

ods excel close;

And running that generates an excel workbook with three worksheets, one for each table and these are labeled.

If you now drop that code into your stored process, then it should work in your web app.

HTH

Mark

[uguraltuntas67]

Hi Mark,

Thank you for your kindly reply. 

I can create an excel on file disk but the problem is i can get many request in the same time and each excel possibly consist of different rows of data.

So i've tried to create a temporary excel on file disk under the user session bu i can not send to _webout that excel.

This is why excel should be downloandable on browser if user want.

I am not sure that creating an excel from multiple table and sending to _webout is even possible. I could not find any document or post online.

Best Regards,

[MarkBodt_NZ]

Actually, it does all work if yuo prepare the code properly for a stored process web app.

In my post I was trying to get across that the best way to troubleshoot this problem (and develop the application) is to simplify everything.

That is, get it all working in EG first before wrapping it in a stored process / web app.

Your problem was around not being able to create an excel workbook with multiple tabs for different tables. So I got this working for you in EG.

Now that that's working, you need to prepare the code and for the stored process / web app, like you've done for the original app that you have.

And one of those preparation steps is changing the file destination from disk to _webout

Here's the proof. I've prepared my code and put it into a stored process and executed the stored process in the Stored Process WebApp:

 And here's the code I put into the stored process:

*ProcessBody;

data _null_;

rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');

run;

* create 3 test data sets;

data one(where=(age=12))

      two(where=(age=13))

      three(where=(age=14));

      set sashelp.class;

run;

*create workbook;

ods excel file = _webout;

ods excel style = Plateau;

ods excel options(sheet_name= "12 year olds");

proc print noobs data=one;

run;

ods excel options(sheet_name= "13 year olds");

proc print noobs data=two;

run;

ods excel options(sheet_name= "14 year olds");

proc print noobs data=three;

run;

ods excel close;