topic Re: ODS Excel - Print value of column once when multiple rows have same value. in ODS and Base Reporting

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

ncsthbell — Tue, 30 Jun 2015 13:35:08 GMT

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

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

ballardw — Tue, 30 Jun 2015 14:58:35 GMT

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.

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

ncsthbell — Tue, 30 Jun 2015 15:15:26 GMT

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;

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

ballardw — Tue, 30 Jun 2015 16:42:12 GMT

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.

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

ncsthbell — Tue, 30 Jun 2015 17:28:19 GMT

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

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

Tom — Tue, 30 Jun 2015 17:40:28 GMT

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;

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

ncsthbell — Tue, 30 Jun 2015 17:43:24 GMT

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

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

Tom — Tue, 30 Jun 2015 17:49:36 GMT

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.

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

ncsthbell — Tue, 30 Jun 2015 17:52:15 GMT

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.