BookmarkSubscribeRSS Feed
ncsthbell
Quartz | Level 8

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

                             


8 REPLIES 8
ballardw
Super User

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.

ncsthbell
Quartz | Level 8

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;

ballardw
Super User

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.

ncsthbell
Quartz | Level 8

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

Tom
Super User Tom
Super User

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;

ncsthbell
Quartz | Level 8

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

Tom
Super User Tom
Super User

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.

ncsthbell
Quartz | Level 8

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.

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
  • 8 replies
  • 1398 views
  • 0 likes
  • 3 in conversation