I posted something similar around August 2011 and managed to "find" a workaround solution. In my orginal post, I described how I wanted to use proc report to create a table of values for varios categories. Immediately after this table, I wanted another table that would show growth rate for all the categories above for select years (1990-2000,2000-2010,2010-2015,2010-2022). After not having any luck creating these growth rates inside proc report, my workaround solution involves creating a temporary dataset to hold these growth rates and then using another proc report statement to display both tables in one sheet. I am creating several tables with eaching having a growth rate table below. By using the "sheet interval" option in excel tagsets ods destination, I am able to accomplish about 90% of what I need. However, I am stuck on some issues relating to formatting values. My requests/questions to the board: (1) In the first proc report, I am using the style overide format STYLE(column)={TAGATTR='format:#,##0'}. For a few columns I would like to use STYLE(column)={TAGATTR='format:#,###.##'} that is display up to 2 decimal places to the right. However, if the value in the column is zero, I get a period. Is there a way to display a zero for values that are zero but display the value up to 2 decimal places to the right? (2) Similar to above but applied to the second proc report - this would be the growth rate table. I would like to display the test '--' for entries where the growth rate is zero - otherwise display the value. (3) In the second proc report (dealing with the growth rates), I would like to insert the text "Annual Growth Rates (%)" but I would like the font to be bold. The code I am using on the compute statement does not accomplish this. Thanks ---------------------- code-------------------------------------------------------- proc template ; define style styles.Cecforms ; parent = styles.sansPrinter ; class fonts / 'TitleFont' = ("Arial",3,bold) 'headingFont' = ("Arial",2) 'docFont' = ("Arial",2) ; class table / borderspacing = 0 cellpadding = 8 borderrightcolor=black borderleftcolor=black borderleftwidth=2 bordertopcolor=white borderbottomcolor=white borderrightwidth=2 bordertopwidth=2 borderbottomwidth=2 ; class header / background = white borderrightcolor=black borderleftcolor=black borderleftwidth=2 bordertopcolor=black borderbottomcolor=black borderrightwidth=2 bordertopwidth=2 borderbottomwidth=2 ; class column / borderrightcolor=black borderleftcolor=black borderleftwidth=2 bordertopcolor=white borderbottomcolor=white borderrightwidth=2 bordertopwidth=0 /* 2 */ borderbottomwidth=1 ; end ; run ; ods tagsets.excelxp file = "&link1\test1.xls" style = Cecforms ; ods tagsets.ExcelXP options(autofit_height = 'no' zoom='80' width_fudge='0.75' embedded_titles = 'yes' row_heights = '10' center_horizontal = 'yes' ) ; ods tagsets.ExcelXP options(sheet_interval = 'proc') ; ods tagsets.ExcelXP options(sheet_interval = 'none' sheet_name="Utility Form 1.2-Mid" absolute_column_width ='10,10,10,10,12,10,10,10' row_heights = '12' ) ; title1 "Form 1.2 - Utility Planning Area" ; title2 "Staff Revised Forecast - Mid Demand Case" ; title3 "Net Energy for Load (GWh)" ; /* create table for net energy for load */ proc report data = Net_load nowd split ='\' ; column Year Mid_c Mid_l Mid_gg Mid_sgnopv Mid_sgpv Mid_sgt Mid_net ; define Year / display ; define Mid_c / "Total\Consumption" display STYLE(column)={TAGATTR='format:#,##0'} ; define Mid_l / "Net Losses" display STYLE(column)={TAGATTR='format:#,##0'} ; define Mid_gg / "Gross\Generation" display STYLE(column)={TAGATTR='format:#,##0'} ; define Mid_sgnopv / "Non-PV\Self Generation" display STYLE(column)={TAGATTR='format:#,##0'} ; define Mid_sgpv / "PV" display STYLE(column)={TAGATTR='format:#,##0'} ; define Mid_sgt / "Total\Private\Supply" display STYLE(column)={TAGATTR='format:#,##0'} ; define Mid_net / "Net Energy\for Load" display STYLE(column)={TAGATTR='format:#,##0'} ; where put(PAN,Pa_id.) = "PGE" ; quit ; /* create table of growth rates */ /* blank out title statements */ title1 ; title2 ; title3 ; /* now the growth rate table */ proc report data = Grwth_net nowd split ='\' /*style(report)=[rules=none frame=void]*/ style(column header)=[borderrightcolor=white borderleftcolor=white borderleftwidth=1 borderrightwidth=1 borderbottomwidth=1 bordertopwidth=1 borderbottomcolor=white bordertopcolor=white ] ; column Period Mid_c2 Mid_l2 Mid_gg2 Mid_sgnopv2 Mid_sgpv2 Mid_sgt2 Mid_net2 ; define Period / "" display style(column)={tagattr='format:###.##%'} ; define Mid_c2 / "" display style(column)={tagattr='format:###.##%'} ; define Mid_l2 / "" display style(column)={tagattr='format:###.##%'} ; define Mid_gg2 / "" display style(column)={tagattr='format:###.##%'} ; define Mid_sgnopv2 / "" display style(column)={tagattr='format:###.##%'} ; define Mid_sgpv2 / "" display style(column)={tagattr='format:###.##%'} ; define Mid_sgt2 / "" display style(column)={tagattr='format:###.##%'} ; define Mid_net2 / "" display style(column)={tagattr='format:###.##%'} ; compute before _page_ / style=[font_weight = bold font_size = 5 just= left] ; line "Annual Growth Rates (%)" ; endcomp ; where put(PAN,Pa_id.) = "Utility" ; quit ; /* now close ods destination */ ods tagsets.excelxp close ;
... View more
