08-11-2014 07:58 PM
I am trying create multiple excel outputs in same excel sheet in stored process.
1. i have created a prompt with static list and multiple values to populate. am not sure about the do loop to make user to select multiple values and get multiple excel outputs either in different tabs of single excel sheet from stored procedure or into multiple excel sheets for each selected value..
i tried using following format, but didnot work
ODS Tagsets.ExcelXP file='file-name.xml'
/*proc print code*/
ODS Tagsets.ExcelXP CLOSE;
how can i create multiple outputs..
08-12-2014 06:40 AM
Well, expanding you code above:
ods tagsets.excelxp file="...\you_filename.xls" style=statistical options (frozen_headers="1");
do loop="Mysheet1", "Mysheet2", "Mysheet3"; /* This is if you want separate sheets */
ods tagsets.excelxp options (sheet_name=&loop);
proc print data=sashelp.cars;
/* if you want all on same sheet then */
ods tagsets.excelxp options (sheet_name="all_in_one");
proc print data=sashelp.cars;
proc print data=sashelp.class;
ods tagsets.excelxp close;
08-12-2014 03:40 PM
But the problem here is, I have created a static prompt with multiple options to select and manually entered values into prompt. Now when we select multiple prompts it should generate promptname_count values based on the valuse selected.
Am writing a code as
%if promptname_count=1 % then
%else %do I=1 %to &promptname_count;
When I run above code ivwas expecting if users selects 2 values count should resolve to 2 and output should come in two excel sheets. Instead am getting a
error 180-322: as statement is not valid or in out of proper order.
Need help how canbi resolve both values in code.so I can apply multiple output option.
08-12-2014 03:47 PM
I don't think there's an option to do what you want. You'll have to basically code two separate options.
08-12-2014 04:07 PM
You'll have to write one section of code to create the output for the option of a single file and a second section of code to deal with the option of multiple files. Dealing with multiple files makes me think you'll need a macro to loop through the program somehow.
There are many ways to put these two sections together, but there isn't a single way to do it that *I* know of at the moment. ODS Excel may have newer options in SAS 9.4.
08-13-2014 04:07 AM
Could you explain exactly what you are doing as I really don't see what the problem is here. As Reeza mentioned create macro logic to parse if one or more files. The way I would do it though is this:
/* Do your prompt or what ever it is here */
/* Put the resulting string or strings into a dataset */
/* Parse the dataset if necessary to get one row per file */
call execute('ods tagsets.excelxp file="...\'||strip(filename)||'.xls";
/* Do you code here */
ods tagsets.excelxp close;');
As for your macro code above, you would need to inform us where the error occurs, what you are running it in, maybe log with mlogic mprint symblogen on. Do you have this code within a %macro / %mend construct?
08-13-2014 04:23 PM
Thank you for above replies!! i was able to fix one of my problems. But still have following issue..
1. am running a stored procedure from sas stored procedure web application and i have a multi select ptompt on top of it.
2. i have selected 2 values from the list and ran and was expecting 2 excel sheets to open with results from 2 tables. but am getting only one excel sheet for the first prompt i selected. is it possible to get two excel sheets or two tabs in one excel? am not able to provide log as i cant see it in web browser...
3. right now am using following code.
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
%if (&name)= box %then %do;
rc = stpsrv_header('Content-type','application/file=box.xls');
%if &name= chair %then %do;
rc = stpsrv_header('Content-type','application/file=Chair.xls');
am not getting any error, but i can see only output for table box even if i select both in my prompt..