The SAS Output Delivery System and reporting techniques

ODS Excel - Print value of column once when multiple rows have same value.

Reply
Frequent Contributor
Posts: 82

ODS Excel - Print value of column once when multiple rows have same value.

I am creating a report using Proc Report and exporting using ODS TAGSETS.EXCELXP. For several columns I want to print the header but only the data value in the column on the first occurrence of the value. Example:  If my columns are 'COUNTY' & 'TOWNSHIP' and there are multiple rows, I want to print the value of  'COUNTY' on the first occurrence of the multiple rows and do the same for 'TOWNSHIP'.

I tried using 'ORDER' as an option in the PROC Report section on the 'Define' statement for the columns but it does not seem to work.  I have read through the options for the ODS Tagsets for excel and cannot find anything that looks like what I am trying to do.

I want report to look like this:

COUNTY               Township

Columbus               Middle Creek

                               Willow Spring

                               Flat Rock  

Verona                    Verona  Island

                                Hampton

I would appreciate any suggestions!  I am not an expert with SAS Reporting Smiley Sad

                             


Super User
Posts: 10,466

Re: ODS Excel - Print value of column once when multiple rows have same value.

Please show your Proc Report code. You likely only need to make a small change but options can interact and a suggestion that would work may not if you have a conflicting property set.

Frequent Contributor
Posts: 82

Re: ODS Excel - Print value of column once when multiple rows have same value.

Here is a sample of my proc report code:  (I also tried using 'order' on the 'County' column and it did not work.

Note: I have noticed that in proc report when I justify a column to be 'left', when it goes into ODS Excel it shows up with a 'right' justification.  (just a side note!)

PROC Report Data=COUNTYTOWNSHIP_Rpt_Tbl nowd contents=""

style(report)=[background=white bordercolor=white
borderwidth=
0 just=center font_face='Times' width=12in]

style(header)=[background=white bordercolor=black
borderwidth=
2 font_face='Times' font_size=11pt]

style(summary)=[background=white fontstyle=roman
fontweight=bold bordercolor=black bordertopwidth=
1PX bordertopstyle=solid
bordertopcolor=black];

COLUMNS County Township Population Avg_Income Avg_nbr_household,

define County / display 'COUNTY' RIGHT 

style(column)=[font_size=9pt width=.8in]

style(header)=[font_size=8pt font_face='Times' font_weight=bold just=left];

 

define Township / display 'TOWNSHIP'

style(column)=[just=left font_size=9pt width=.8in]

style(header)=[just=left font_size=8pt font_face='Times' font_weight=bold];

define Population / display 'POPULATION'

style(column)=[just=left font_size=9pt width=2.0in]

style(header)=[just=left font_size=8pt font_face='Times'
font_weight=bold];

  

define Avg_Income / display 'AVERAGE INCOME'

style(column)=[font_size=9pt width=1.5in]

style(header)=[font_size=8pt font_face='Times' font_weight=bold just=left];

define Avg_nbr_household / display 'AVERAGE HOUSEHOLD NBR'

style(column)=[font_size=9pt width=1.0in]

style(header)=[font_size=8pt font_face='Times' font_weight=bold just=left];

  

run;

Super User
Posts: 10,466

Re: ODS Excel - Print value of column once when multiple rows have same value.

I think you want

define County / group 'COUNTY' RIGHT 

and similar with township, group not display.

Sometimes with tagsets.Excelxp you need to provide tagattr options for specific Excel appearance behaviors.

Frequent Contributor
Posts: 82

Re: ODS Excel - Print value of column once when multiple rows have same value.

I already tried 'group' and that did not work.  I figured it was something with ODS tagsets.Excelxp options and I have searched and can not seem to find the right option to handle this.  I'll keep looking.  Thanks

Super User
Super User
Posts: 6,495

Re: ODS Excel - Print value of column once when multiple rows have same value.

Start simple and keep adding back your options until it breaks.

data have;

infile cards dsd dlm='|';

length county township $40;

input county township;

cards;

Columbus|MiddleCreek

Columbus|WillowSpring

Columbus|FlatRock

Verona|VeronaIsland

Verona|Hampton

;;;;

proc report;

  column county township;

  define county / group ;

run;

Frequent Contributor
Posts: 82

Re: ODS Excel - Print value of column once when multiple rows have same value.

Are saying to remove the ODS Tagset.excelxp and just try Proc Report?

Super User
Super User
Posts: 6,495

Re: ODS Excel - Print value of column once when multiple rows have same value.

It works when just using simple proc report above to default HTML output.

It works when using the simple proc report and ODS EXCEL destination (using SAS 9.4).

So try it with TAGSETS.EXCELXP and see if it works.

Then keep adding your styles and see if one of them breaks it.

Frequent Contributor
Posts: 82

Re: ODS Excel - Print value of column once when multiple rows have same value.

Ok... I'll give it a try!  Thanks. I'll let you know how it goes, may be tomorrow before I get back to it.

Ask a Question
Discussion stats
  • 8 replies
  • 651 views
  • 0 likes
  • 3 in conversation