topic Re: ODS TAGSETS.EXCELXP in SAS Programming

ODS TAGSETS.EXCELXP

ss171 — Thu, 22 Jul 2021 13:55:25 GMT

Hi ,

I need to do two bwlow tasks :

1: I have three reports/table , want all them to be placed horizontally in excel (sheet_interval='none') work to place them vertially

can you suggest way in ods tagsets.excelxp

2: I have a report as a sample attached where there are multiple Desc and based on that creating different columns

But I dont want the first heading in proc report output (Ex: Desc)

How can I do that . Below is the code I have used

proc report data=Sample
style(report)=[font=(calibri, 10pt) just=left]
style(column header)=[font=(calibri,12pt) font_weight = bold just=left]
style(column)=[font=(calibri, 10pt)] nowd;
column ('ID' ID) desc, Var;
define ID / group ' ';
define desc / across order=data;
define var / analysis sum style={tagattr='format:#,##0;[RED]-#,##0'} '';
run;

Re: ODS TAGSETS.EXCELXP

Sajid01 — Thu, 22 Jul 2021 19:11:25 GMT

1. Use ODS EXCEL . (ods tagsets.excelxp produces files that are in reality xml files and are bloated -large in size. )
2.There are number of resources available . For example a good starting point is https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Re: ODS TAGSETS.EXCELXP

ss171 — Fri, 23 Jul 2021 05:30:28 GMT

Yes, but issue is in placing tables in horizontal way (not having any start point) . I have gone through many blogs but didnt get a solution

Re: ODS TAGSETS.EXCELXP

Sajid01 — Fri, 23 Jul 2021 08:56:53 GMT

Have no idea how your source data looks like.
Can you share the data used for this sample report?

Re: ODS TAGSETS.EXCELXP

ss171 — Fri, 23 Jul 2021 09:37:15 GMT

@Sajid01 I have attached sample so there can be multiple proc results . I want them to be placed horizontally

Re: ODS TAGSETS.EXCELXP

Sajid01 — Fri, 23 Jul 2021 12:51:40 GMT

Thanks.
I don't see any correlation between your original spreadsheet and the current one.

Re: ODS TAGSETS.EXCELXP

ss171 — Fri, 23 Jul 2021 12:58:39 GMT

@Sajid01
There seems to be a confusion.
In the top query I asked for two tasks
One with proc report which is resolved by across ' ' .. syntax
Thanks
For other task I have attached sample excel if can help
Thanks

Re: ODS TAGSETS.EXCELXP

Cynthia_sas — Fri, 23 Jul 2021 16:38:12 GMT

Hi:

It is easier to place output from different procedures on one page horizontally using ODS PDF and ODS LAYOUT, as shown below:

This type of ODS LAYOUT is not supported for the ODS EXCEL destination, so this code would not work for Excel.

Since this capability to place procedure output horizontally is not available for ODS EXCEL You'd have to do a lot of data manipulation to create one data set with multiple columns to make this work in Excel. Here's an example:

In the above example, I just used 2 different datasets side by side. Assuming that your procedure of interest makes an output dataset, then you could do something similar but it is a lot of work to do something that ODS PDF does quite easily.

Cynthia

Re: ODS TAGSETS.EXCELXP

ss171 — Fri, 23 Jul 2021 16:43:43 GMT

Ohhh Thanks @cynthia
Really nice explaination