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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
MarkBodt_NZ
Obsidian | Level 7

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

Excel.png

View solution in original post

3 REPLIES 3
MarkBodt_NZ
Obsidian | Level 7

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

Excel.png

uguraltuntas67
Fluorite | Level 6

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
Obsidian | Level 7

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:

CAS_43.png

 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;

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 509 views
  • 0 likes
  • 2 in conversation