BookmarkSubscribeRSS Feed
Calcite | Level 5

Hi All,

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



Diamond | Level 26 RW9
Diamond | Level 26

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;


end loop;

/* if you want all on same sheet then */

ods tagsets.excelxp options (sheet_name="all_in_one");

proc print;


proc print data=sashelp.class;



ods tagsets.excelxp close;

Calcite | Level 5

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 I can apply multiple output option.


Super User

I don't think there's an option to do what you want. You'll have to basically code two separate options.

Calcite | Level 5

Can you be precise!! I didn't get wht you mean by two seperate codes??

Super User

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.

Diamond | Level 26 RW9
Diamond | Level 26


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 */

data _null_;

     set list_of_files;

     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?

Calcite | Level 5

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.


*%let _ODSDEST=TAGSETS.Msoffice2k;

  data _null_;

rc = stpsrv_header('Content-type','application/');

%macro A;

%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');


%mend A;


am not getting any error, but i can see only output for table box even if i select both in my prompt..





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!

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.

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
  • 7 replies
  • 3 in conversation