topic Re: excel tagset and putting two tables in ODS and Base Reporting

excel tagset and putting two tables

asishgautam — Thu, 25 Apr 2013 16:27:27 GMT

Hi all,

I am trying to put 2 tables one right below the other. But for some reason, the row separating the two tables has a really large row height and I can't figure why this is happening. Code below and a screen shot:

This is a screen shot of the sheet having the label "Form 1.1..." As you can the area I highlighted in yellow is a large row height - how can I fix this so the row height is the same as the row height of rows right below the highlighted section with text...? Any immediate help would be appreciated as I

have a lot of tables to create...:(

Code:

%macro cecform ;

%let Scen = High Mid Low ;

%let Panum = BUGL IID LADWP PASD PGE SCE SDGE SMUD STATEWIDE ;

%let Pagas = SDGE SCG PGE OTH STATE ;

%let i = 1 ; /* iterate over scenarios */

ods listing close ;

ods results = off ;

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

%let temps = %scan(&Scen,&i) ;

%let Tit2 = California Energy Demand 2012-2022 Adopted Forecast - &temps Demand Case ;

%let j = 1 ; /* iterate over planning areas */

%do %until (%scan(&Panum,&j) eq ) ;

%let temppa = %scan(&Panum,&j) ;

/* test for directory location */

%if &temps = High %then %do ;

%let dir = &outdir.\High ;

%end ;

%else %if &temps = Mid %then %do ;

%let dir = &outdir.\Mid ;

%end ;

%else %let dir = &outdir.\Low ;

/* final output file for cec forms */

%let dir2 = "&dir.\&temppa._&temps..xls" ;

/* now open up ods destination */

ods tagsets.excelxp

file = &dir2

style = Cecforms ;

ods tagsets.ExcelXP options(autofit_height = 'no'

zoom='80'

width_fudge='0.75'

embedded_titles = 'yes'

/*row_heights = '10'*/

center_horizontal = 'yes'

) ;

/* create table of contents */

ods tagsets.ExcelXP options(sheet_name="List of Forms"

absolute_column_width ='75'

/*row_heights = '16'*/

) ;

/* blank out title statements */

title1 ;

title2 ;

title3 ;

proc report data = Toc 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

font_weight = bold font_size = 2 just = left] ;

column Toc ;

define Toc / "&Tit2" ;

compute before ;

line @1 "June 2012" ;

line @1 " " ;

line @1 "List of Forms" ;

line @1 " " ;

line @1 "Form 1.1: Total Electricity Consumption by Sector" ;

line @1 "Form 1.1b: Electricity Sales by Sector (equals consumption minus self-generation)" ;

line @1 "Form 1.2: Net Energy for Load (equals consumption plus losses minus self-generation)" ;

line @1 "Form 1.3: Coincident Peak by Sector:" ;

line @1 "Form 1.4: Net Peak Demand (equals sum of peaks by sector plus losses minus self-generation" ;

%if &temppa ne STATEWIDE %then %do ;

line @1 "Form 1.5: Extreme Temperature Peak Demand" ;

%end ;

line @1 "Form 1.7a: Private Supply by Sector" ;

line @1 "Form 2.2: Economic and Demographic Assumptions" ;

%if &temppa ne STATEWIDE %then %do ;

line @1 "Form 2.3: Electricity Prices by Sector" ;

%end ;

endcomp ;

quit ;

/* create table for consumption */

ods tagsets.ExcelXP options(sheet_interval = 'proc') ;

ods tagsets.ExcelXP options(sheet_interval = 'none'

sheet_name="&temppa Form 1.1-&temps"

absolute_column_width ='10,10,16,10,16,12,10,10,8,10,12'

) ;

%if &temppa ne STATEWIDE %then %do ;

title1 "Form 1.1 - &temppa Planning Area" ;

%end ;

%else %if &temppa eq STATEWIDE %then %do ;

title1 "Form 1.1 - &temppa" ;

%end ;

title2 "&Tit2" ;

title3 "Electricity Consumption by Sector (GWh)" ;

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

column Year Res_c_&temps Res_ev_c_&temps Com_c_&temps Com_ev_c_&temps Manuf_c_&temps

Mine_c_&temps Ag_c_&temps Tcu_c Street_c Tot_c_&temps ;

define Year / display ;

define Res_c_&temps / "Residential" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Res_ev_c_&temps / "Residential Electric\Vehicles*" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Com_c_&temps / "Commercial" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Com_ev_c_&temps / "Commercial Electric Vehicles*" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Manuf_c_&temps / "Manufacturing" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Mine_c_&temps / "Mining" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Ag_c_&temps / "Agricultural" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Tcu_c / "TCU" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Street_c / "Street\Lighting" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Tot_c_&temps / "Total\Consumption" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

compute after/style=[just=l] ;

line @1 " " ;

line @1 "* Residential and commercial electric vehicle consumption included in residential and commercial totals." ;

line @1 "Last historic year is 2010. Consumption includes self-generation." ;

endcomp ;

where put(PAN,Pa_id.) = "&temppa" ;

quit ;

/* create table of growth rates */

/* blank out title statements */

title1 ;

title2 ;

title3 ;

/* now the growth rate table */

proc report data = Grwth_c 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 ]

style(lines)=[font_weight = bold font_size = 3] ;

column Period Res_&temps Res_ev_&temps Com_&temps Com_ev_&temps Manuf_&temps Mine_&temps

Ag_&temps Tcu2_&temps Stre_&temps Tot_&temps ;

define Period / "" display ;

define Res_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Res_ev_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Com_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Com_ev_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Manuf_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Mine_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Ag_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Tcu2_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Stre_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

define Tot_&temps / "" display style(column) = {tagattr='format:###.##%;[black]-###.##%;[black]--;'} ;

compute before ;

line @1 "Annual Growth Rates (%)" ;

endcomp ;

where put(PAN,Pa_id.) = "&temppa" ;

quit ;

/* create table for sales */

ods tagsets.ExcelXP options(sheet_interval = 'proc') ;

ods tagsets.ExcelXP options(sheet_interval = 'none'

sheet_name="&temppa Form 1.1b-&temps"

absolute_column_width ='10,10,10,10,10,10,8,10,10'

) ;

%if &temppa ne STATEWIDE %then %do ;

title1 "Form 1.1b - &temppa Planning Area" ;

%end ;

%else %if &temppa eq STATEWIDE %then %do ;

title1 "Form 1.1b - &temppa" ;

%end ;

title2 "&Tit2" ;

title3 "Electricity Sales by Sector (GWh)" ;

/* create table for sales */

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

column Year Res_s_&temps Com_s_&temps Manuf_s_&temps Mine_s_&temps

Ag_s_&temps Tcu_s Street_s Tot_s_&temps ;

define Year / display ;

define Res_s_&temps / "Residential" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Com_s_&temps / "Commercial" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Manuf_s_&temps / "Manufacturing" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Mine_s_&temps / "Mining" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Ag_s_&temps / "Agricultural" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Tcu_s / "TCU" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Street_s / "Street\Lighting" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

define Tot_s_&temps / "Total Sales" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

compute after/style=[just=l] ;

line @1 " " ;

line @1 "Last historic year is 2010. Sales excludes self-generation." ;

endcomp ;

where put(PAN,Pa_id.) = "&temppa" ;

quit ;

/* create table of growth rates */

/* blank out title statements */

title1 ;

title2 ;

title3 ;

/* now the growth rate table */

proc report data = Grwth_s 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 ]

style(lines)=[font_weight = bold font_size = 3] ;

column Period Res_&temps Com_&temps Manuf_&temps Mine_&temps