The SAS Output Delivery System and reporting techniques

how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

Reply
Occasional Contributor
Posts: 9

how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

Hi,

Can you guys help me out how to print two reports with different number of columns into a single tab  using ods tagsets.excelxp?

Ex:

If the 1st report has 4 columns and the length of each column is 20

i.e. i have given absolute_column_width='20,20,20,20'

and the 2nd report has 13 columns

i.e. absolute_column_width='20,5,5,5,5,5,5,5,5,5,5,5,5'

the below is the code that is used for generation of the reports

ods tagsets.excelxp

options( Sheet_name = 'Sample report'

Orientation = 'landscape'

Sheet_Interval = 'none'

zoom = '90'

skip_space='0,0,0,0,0'

ROW_HEIGHTS='4,0,0,0,0'

Pages_FitWidth = '1'

Pages_FitHeight = '100'

embedded_titles='yes'

Embedded_Footnotes = 'yes'

Print_Header = ''

Autofit_Height = 'yes'

/*Absolute_Column_Width = '10,6,6,6,6,6,6,6,6,6'*/

DEFAULT_COLUMN_WIDTH = '20,20,20,20'

noborder= 'YES'

rules='none'

);

1st report

ods tagsets.excelxp

options( zoom = '90'

skip_space='0,0,0,0,0'

ROW_HEIGHTS='4,0,0,0,0'

Pages_FitWidth = '1'

Pages_FitHeight = '100'

embedded_titles='yes'

Embedded_Footnotes = 'yes'

Print_Header = ''

Autofit_Height = 'yes'

DEFAULT_COLUMN_WIDTH = '20,5,5,5,5,5,5,5,5,5,5,5,5'

noborder= 'YES'

rules='none'

);

2nd report

;

The problem i am facing is :

1st report is coming fine and the problem is with the 2nd report

i.e. first 4 columns are taking the width from the first report options.

can you guys tell me if there is any other option that is to be used for getting the report into shape.

Thanks,

Reddikrishna

Grand Advisor
Posts: 10,196

Re: how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

The problem i am facing is :

1st report is coming fine and the problem is with the 2nd report

i.e. first 4 columns are taking the width from the first report options.

can you guys tell me if there is any other option that is to be used for getting the report into shape.

Thanks,

Reddikrishna

The column width is Excel behavior that you can't change. If you are report and there is no need for additional calculations perhaps RTF or PDF output would work. Then you can control appearance per table using the procedures' appearance controls.

SAS Super FREQ
Posts: 8,716

Re: how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

Hi:

  The behavior that you note is how TAGSETS.EXCELXP implements the SHEET_INTERVAL='NONE'  suboption -- by taking your two separate tables and making them 1 table. If you used SHEET_INTERVAL='TABLE', then you would get 2 sheets and each sheet would have its own widths, as you set.

  I do not believe there is a way to do what you want. The question I always ask myself in situations like this is how would I do this in Excel if SAS were not involved. So I went to Excel and typed some numbers into cells to simulate the information from 2 separate tables. (see screenshot). As far as I can tell with Excel, the WIDTH of the entire column A is 1 width. I tried, but did not figure out how, to set the A6, A7, A8 and A9 cells a different width than the A1, A2, A3, A4 cells. Do you know how to do this in Excel without SAS being involved??? Every time I highlighted a group of cells and changed the width, the width of the WHOLE column changed. So, if Excel will not allow you to change widths within a column, I'm not sure you can expect TAGSETS.EXCELXP to behave differently.

  Otherwise, if you can do what you want in Excel (set different widths within 1 column) without SAS being involved, then your second challenge is to save the Excel file as Spreadsheet Markup Language XML and see whether the differing widths are allowed in the XML definition of a spreadsheet. You might check with Tech Support on this, but I think that what you want is not possible.

cynthia

Attachment
Grand Advisor
Posts: 10,196

Re: how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

The only way I've thought that works to have this appearance in Excel is to have merged cells in the first table to allow the second table to have narrower widths. But I wouldn't want to do that for much data manually as you have to do it 2 or 3 cells on a single row at a time. The data from SAS would have to be output with empty column or more between each value on the row that wanted the merge due to Excel wanting to combine the text in merged cells. You'd probably want to create an Excel macro or VB code if more than a few rows of data were involved.

Grand Advisor
Posts: 17,313

Re: how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

There is a merge across option in tagsets, but I've never managed to make it work.

Occasional Contributor
Posts: 9

Re: how to print two reports with different number of columns into a single tab using ods tagsets.excelxp?

Thanks for all your comments.......

Ask a Question
Discussion stats
  • 5 replies
  • 432 views
  • 0 likes
  • 4 in conversation