BookmarkSubscribeRSS Feed
Calcite | Level 5
I'm trying to format a spreadsheet using the tagset.ExcelXP tags and am having problems trying to figure out if the following is possible. The ExcelXP tagset is being used to add filters, frozen headers and multiple worksheets.

Adding a two line header in the report that spans 9 cells for each line. I unable to find an example of this. Is it possible to add a two line embedded title to the spreasheet and specify how many columns it will span?

Changing how the data is being placed. The output currently follows the standard format with the labels across the top and the data filling in the cells below. Is it possible to change the data layout?

We are trying to acheive something similar to the following:

Header1 (span 9 columns)
Header2 (span 9 columns)

Label1 Value1 Label4 Value4
Label2 Value2 Label5 Value5
Label3 Value3

Label1 Value1 Label4 Value4
Label2 Value2 Label5 Value5
Label3 Value3

Instead of:

Label1 Label2 Label3 Label4 Label5
Value1 Value2 Value3 Value4 Value5
Value1 Value2 Value3 Value4 Value5
SAS Employee
First, anything is possible. However, the tagset does not currently do these things.

We can probably get very close just by using system titles and a table template.

If the headers are left justified, using a system title should work just like
you want. In the left justified case, no spanning is specified and Excel just
displays the text across all adjoining cells.

The table layout looks very much like a phone book type layout. I think this can be done with a table template, although I'm not the expert on this.
I'll ask our expert to take a look.

If the table template cannot do it, I can certainly make the tagset do it. But it will take a bit of work. The tagset has arrays, so it could save the column headers and the data values for each row and rearrange them accordingly. The result would be a custom Excel tagset that has this
specific behavior, it should also be possible to turn the behavior on and off.

Let me know if you want to pursue this direction. It is not likely that I could get to it until sometime in January. If you would like to attempt it yourself I would be happy to give you guidance.

Another way to do it would be to rearrange your data into a data set that reflects this shape. That might be easier than anything else.
SAS Employee
Table templates won't be able to do what you want. Eric's suggestion about using titles, and rearranging the dataset is the only way that I think you would be able to do what you want.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 3 in conversation