BookmarkSubscribeRSS Feed
krishnaboosireddi
Calcite | Level 5

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

5 REPLIES 5
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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


just_excel_behavior.png
ballardw
Super User

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.

Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1069 views
  • 0 likes
  • 4 in conversation