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
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.
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;
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.
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
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;
Are saying to remove the ODS Tagset.excelxp and just try Proc Report?
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.