BookmarkSubscribeRSS Feed
nith
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..

Thanks

Nitin

7 REPLIES 7
RW9
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 data=sashelp.cars;

     run;

end loop;

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

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

proc print data=sashelp.cars;

run;

proc print data=sashelp.class;

run;

...

ods tagsets.excelxp close;

nith
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

"&promptname";

%else %do I=1 %to &promptname_count;

"&&promptname&i";

%End;

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.

Thanks!!

Reeza
Super User

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

nith
Calcite | Level 5

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

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

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

run;

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?

nith
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/vnd.ms-excel');

%macro A;

%if (&name)= box %then %do;

rc = stpsrv_header('Content-type','application/file=box.xls');

%end;

%if &name= chair %then %do;

rc = stpsrv_header('Content-type','application/file=Chair.xls');

%end;

%mend A;

%A;

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

Thanks

nitin

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 3750 views
  • 0 likes
  • 3 in conversation