help with setting format from sas ods to excel

asishgautam · Posted 08-11-2011 05:58 PM

hi all,

please help! i have a lot of tables to create manually if i can't figure this out!

i would like to format my tables in excel such that: there is an outline or box line enclosing my table, my column headers are enclosed in a border, and each column has lines (bolded). my research suggests that the following options, when defining a custom style should do the trick:

rules = cols

frame = box

below is the format i am looking for:

instead, i get this format:

however, i am not getting the desired formatted output. Am I forgetting an option? How can I fix this?

/* set up style format */

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

borderwidth = 2

rules = cols

frame = box

cellpadding = 8

;

class header /

background = white

;

end ;

run ;

/* set up macro to create tables */

%macro Cefftab ;

%let Uncnt = Statewide PGE SCE SDGE SMUD LADWP ;

%let Scen = Low Demand,Mid Demand,High Demand ;

ods listing close ;

ods tagsets.excelxp

body = 'C:\Documents and Settings\agautam\Desktop\Selfgen\CED 2011\Preliminary\Summary_files_chris\summary_ee\EE_Tables_test2.xml'

style = Cecforms ;

%let i = 1 ;

%do %until (%scan(&Uncnt,&i) eq ) ;

%let tempid = %scan(&Uncnt,&i) ;

%let j = 1 ;

%do %until (%scan(%quote(&Scen),&j,',') eq ) ;

%let tempsc = %scan(%quote(&Scen),&j,',') ;

ods tagsets.ExcelXP options(absolute_column_width ='8,4,8,8,8,8,8,8,8,12,8'

width_fudge='0.75' embedded_titles = 'yes') ;

ods tagsets.ExcelXP options(sheet_name="&tempid.rescon-&tempsc") ;

title1 "Table A-8: Energy Efficiency/Conservation Savings (GWh)" ;

title2 "California Energy Demand 2011-2022 Staff Preliminary Forecast " ;

title3 " Residential &tempsc Scenario" ;

proc report data = Rescon nowd split ='\' ;

column PA Year ('Committed' BS AS Prog_price_and_other_effects total_committed)

('Uncommitted' Standards Programs Big_Bold total_uncommitted) total_savings ;

define PA / group ;

define Year / group ;

define BS / 'Building\Standards' analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define AS / 'Appliance\Standards' analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define Prog_price_and_other_effects / analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define total_committed / 'Total\Committed ' analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define Standards / analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define Programs / analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define Big_Bold / analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define total_uncommitted / 'Total\Uncommitted' analysis STYLE(column)={TAGATTR='format:#,##0'} ;

define total_savings / 'Total\Savings' analysis STYLE(column)={TAGATTR='format:#,##0'} ;

where PA eq "&tempid" and Scenario eq "&tempsc" and Year between 1990 and 2022 ;

run ;

ods tagsets.ExcelXP options(sheet_name="&tempid.respeak-&tempsc") ;

title1 "Table A-8: Energy Efficiency/Conservation Peak Savings (MW)" ;

title2 "California Energy Demand 2011-2022 Staff Preliminary Forecast " ;

title3 "Residential &tempsc Scenario" ;

proc report data = Respeak nowd split ='\' ;