Your SAS programs, embedded in web apps and elsewhere

creating output in multiple excel of single excel or in seperate excel sheets

Reply
Occasional Contributor
Posts: 8

creating output in multiple excel of single excel or in seperate excel sheets

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

Super User
Super User
Posts: 7,942

Re: creating output in multiple excel of single excel or in seperate excel sheets

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;

Occasional Contributor
Posts: 8

Re: creating output in multiple excel of single excel or in seperate excel sheets

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

Super User
Posts: 19,772

Re: creating output in multiple excel of single excel or in seperate excel sheets

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

Occasional Contributor
Posts: 8

Re: creating output in multiple excel of single excel or in seperate excel sheets

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

Super User
Posts: 19,772

Re: creating output in multiple excel of single excel or in seperate excel sheets

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.

Super User
Super User
Posts: 7,942

Re: creating output in multiple excel of single excel or in seperate excel sheets

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?

Occasional Contributor
Posts: 8

Re: creating output in multiple excel of single excel or in seperate excel sheets

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

Ask a Question
Discussion stats
  • 7 replies
  • 2062 views
  • 0 likes
  • 3 in conversation