topic Re: its possible to add more then two tables in none excel sheet. in ODS and Base Reporting

its possible to add more then two tables in none excel sheet.

Shantaram — Wed, 04 May 2016 08:11:53 GMT

Hi,

Its possible to add more then two tables in none excel sheet (xlsx format).

Eg. I have 2 tables and need to display in one excel sheet. it's possible or not. If possible then how?.

Re: its possible to add more then two tables in none excel sheet.

RW9 — Wed, 04 May 2016 08:29:01 GMT

Yes it is possible. In pre 9.4 days, and probably still, the best method is ods tagsets.excelxp + proc report:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

This doesn't create a native Excel file, but an XML file that Excel can read and interpret, but gives great flexibility and formatting.

Then there is libname excel - which is newer (9.4), and creates native files. It didn't seem as flexibile or useful to me though at the moment:

http://www2.sas.com/proceedings/sugi31/024-31.pdf

There are other methods, but I would go with the above first. Proc export tends to be a best guess, DDE is far to old to be used etc.

Re: its possible to add more then two tables in none excel sheet.

LinusH — Wed, 04 May 2016 08:43:28 GMT

Another option would be to use Add-in for MS Office, where the Excel report pulls data from SAS. And you can design your Excel report exactly the way you wish.

Re: its possible to add more then two tables in none excel sheet.

Reeza — Wed, 04 May 2016 08:48:20 GMT

In SAS 9.4 there's also ODS Excel, similar to ODS tagsets which does generate a native excel file.

Proc of export and XLSX will also support multiple sheets in SAS 9.4+

Re: its possible to add more then two tables in none excel sheet.

Shantaram — Wed, 04 May 2016 10:15:30 GMT

Hi,

please find attachment for more details.

table one # New Appointments fixed

teble two # Meetings scheduled (due).

i want both in same sheet.

Re: its possible to add more then two tables in none excel sheet.

LinusH — Wed, 04 May 2016 10:36:58 GMT

@Shantaram, you've had already received some input.

So the next step is that you explore those options - and then return with any relevant follow-up questions if necessary.

Re: its possible to add more then two tables in none excel sheet.

Shantaram — Wed, 04 May 2016 12:14:38 GMT

Hi Linush,

I got your point but i want output in one page not in two diffrent pages.

Re: its possible to add more then two tables in none excel sheet.

Reeza — Wed, 04 May 2016 12:36:44 GMT

ODS tagsets.excelxp file='sample.xml' options(sheet_interval=none);

proc print data=sashelp.class;

proc print data=sashelp.air;

run;

ods tagsets.excelxp close;

Change the file destination to somewhere that makes sense for you and open the resulting file in Excel.

Re: its possible to add more then two tables in none excel sheet.

BrunoMueller — Wed, 04 May 2016 12:44:44 GMT

check out the ODS EXCEL destination

code will look similar to

ods excel file="c:\temp\sample.xlsx" options(SHEET_INTERVAL=  'NONE');

proc print data=sashelp.class;
  where sex = "F";
run;

proc print data=sashelp.class;
  where sex = "F";
run;

ods excel close;

Bruno