excel tagset and putting two tables

[asishgautam]

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  

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

                define Period / "" display ;

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

                define Com_&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 net energy for load */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 1.2-&temps"

                                        absolute_column_width ='10,10,10,10,12,10,10,10'

                                        row_heights = '12'

                                        ) ;

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 1.2 - &temppa Planning Area" ;

            %end ;

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

                title1 "Form 1.2 - &temppa" ;

            %end ;

            title2 "&Tit2" ;

            title3 "Net Energy for Load (GWh)" ;

            /* create table for net energy for load */

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

                column Year &temps._c &temps._l &temps._gg &temps._sgnopv &temps._sgpv &temps._sgt &temps._net ;

                define Year / display ;

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

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

                define &temps._gg / "Gross\Generation" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                define &temps._sgnopv / "Non-PV\Self Generation" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

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

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

                define &temps._net / "Net Energy\for Load" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                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_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    ]

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

                column Period &temps._c2 &temps._l2 &temps._gg2 &temps._sgnopv2 &temps._sgpv2 &temps._sgt2 &temps._net2 ;

                define Period / "" display ;

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

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

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

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

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

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

                define &temps._net2 / "" 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 coin peak */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 1.3-&temps"

                                        absolute_column_width ='10,10,12,10,12,10,10,10,10'

                                        row_heights = '12'

                                        ) ;

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 1.3 - &temppa Planning Area" ;

            %end ;

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

                title1 "Form 1.3 - &temppa" ;

            %end ;

            title2 "&Tit2" ;

            title3 "Coincident Peak Demand by Sector (MW)" ;

            /* create table for coin peak */

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

                column Year Res_&temps Res_ev_pk_&temps Com_&temps Com_ev_pk_&temps Ind_&temps Agw_&temps Oth_&temps Tot_&temps ;

                define Year / display ;

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

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

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

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

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

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

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

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

                compute after/style=[just=l] ;

                    line @1 " " ;

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

                      line @1 "Last historic year is 2011." ;

                  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_coin_pk 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 Res2_&temps Res2_ev_pk_&temps Com2_&temps Com2_ev_pk_&temps

                       Ind2_&temps Agw2_&temps Oth2_&temps Tot2_&temps ;

                define Period / "" display ;

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

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

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

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

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

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

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

                define Tot2_&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 coin peak 2 */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 1.4-&temps"

                                        absolute_column_width ='10,12,10,12,14,10,14,12,12'

                                        row_heights = '12'

                                        ) ;

            /* create table for coin peak 2 */

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 1.4 - &temppa Planning Area" ;

            %end ;

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

                title1 "Form 1.4 - &temppa" ;

            %end ;

            title2 "&Tit2" ;

            title3 "Peak Demand (MW)" ;

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

                column Year Tot_&temps Loss_&temps Gg_&temps Sg_nopv_&temps Sg_pv_&temps Sg_tot_&temps

                       Net_pk_&temps Load_f_&temps ;

                define Year / display ;

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

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

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

                define Sg_nopv_&temps / "Non-PV Self\Generation" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

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

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

                define Net_pk_&temps / "Net Peak\Demand" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                define Load_f_&temps / "Load Factor\(%)" display style(column) = {tagattr='format:#,###.##;[black]-#,###.##;[black]0;'} ;

                compute after/style=[just=l] ;

                    line @1 " " ;

                      line @1 "Last historic year is 2011." ;

                  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_coin2_pk 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 Tot2_&temps Loss2_&temps Gg2_&temps Sg2_nopv_&temps Sg2_pv_&temps

                       Sg2_tot_&temps Net2_pk_&temps Load2_f_&temps ;

                define Period / "" display ;

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

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

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

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

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

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

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

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

                compute before ;

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

                endcomp ;

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

            quit ;

            /* table for extreme tempreature peak scneario */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 1.5-&temps"

                                        absolute_column_width ='10,12,12,12,12,12,12,12'

                                        row_heights = '12'

                                        ) ;

            /* create table for extreme tempreature peak - not for statewide */

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 1.5 - &temppa Planning Area" ;

                title2 "&Tit2" ;

                title3 "Extreme Tempreature Peak Demand (MW)" ;

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

                    column Year Net_pk_&temps One_five One_five_t One_ten One_ten_t One_twent One_twent_t ;

                    define Year / display ;

                    define Net_pk_&temps / "1-in-2\Temperatures" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                    define One_five / "1-in-5\Multiplier" computed style(column) = {tagattr='format:#,###.###;[black]-#,###.###;[black]0;'} ;

                    define One_five_t / "1-in-5\Temperatures" computed style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                    define One_ten / "1-in-10\Multiplier" computed style(column) = {tagattr='format:#,###.###;[black]-#,###.###;[black]0;'} ;

                    define One_ten_t / "1-in-10\Temperatures" computed style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                    define One_twent / "1-in-20\Multiplier" computed style(column) = {tagattr='format:#,###.###;[black]-#,###.###;[black]0;'} ;

                    define One_twent_t / "1-in-20\Temperatures" computed style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                    /* 1 - 5 scalar */

                    compute One_five ;

                        One_five = &&One_five_&temppa ;

                    endcomp ;

                    /* 1 - 5 temp */

                    compute One_five_t ;

                        One_five_t = One_five*Net_pk_&temps ;

                    endcomp ;

                    /* 1 - 10 scalar */

                    compute One_ten ;

                        One_ten = &&One_ten_&temppa ;

                    endcomp ;

                    /* 1 - 10 temp */

                    compute One_ten_t ;

                        One_ten_t = One_ten*Net_pk_&temps ;

                    endcomp ;

                    /* 1 - 20 scalar */

                    compute One_twent ;

                        One_twent = &&One_twent_&temppa ;

                    endcomp ;

                    /* 1 - 20 temp */

                    compute One_twent_t ;

                        One_twent_t = One_twent*Net_pk_&temps ;

                    endcomp ;

                    where put(PAN,Pa_id.) = "&temppa" and Year ge 2011 ;

                quit ;

            %end ;

            /* create table for private supply */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 1.7a-&temps"

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

                                        ) ;

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 1.7a - &temppa Planning Area" ;

            %end ;

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

                title1 "Form 1.7a - &temppa" ;

            %end ;

            title2 "&Tit2" ;

            title3 "Private Supply by Sector (GWh)" ;

            /* create table for private supply */

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

                column Year Res_&temps Com_&temps Manuf_&temps Mine_&temps Ag_&temps Tcu Tot_&temps ;

                define Year / display ;

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

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

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

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

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

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

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

                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_ps 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 Res2_&temps Com2_&temps Manuf2_&temps Mine2_&temps  

                       Ag2_&temps Tcu2_&temps Tot2_&temps ;

                define Period / "" display ;

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

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

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

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

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

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

                define Tot2_&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 econ-demo */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 2.2-&temps"

                                        absolute_column_width ='10,14,14,16,18,18,18'

                                        ) ;

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 2.2 - &temppa Planning Area" ;

            %end ;

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

                title1 "Form 2.2 - &temppa" ;

            %end ;

            title2 "&Tit2" ;

            title3 "Planning Area Economic and Demographic Assumptions" ;

            /* create table for econ-demo */

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

                column Year Hhpop_&temps Numhh_&temps Pphh_&temps Hhinc_&temps Manuf_&temps Floor_&temps ;

                define Year / display ;

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

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

                define Pphh_&temps / "Persons per\Household" display style(column) = {tagattr='format:#,###.##;[black]-#,###.##;[black]0;'} ;

                define Hhinc_&temps / "Real Household\Income\ (Millions 2010$)" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                define Manuf_&temps / "Manufacturing\Output\(Millions 2010$)" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                define Floor_&temps / "Commercial\Floorspace\(MM Sqft.)" display style(column) = {tagattr='format:#,##0;[black]-#,##0;[black]0;'} ;

                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_ed 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 Hhpop2_&temps Numhh2_&temps Pphh2_&temps Hhinc2_&temps Manuf2_&temps Floor2_&temps ;

                define Period / "" display ;

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

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

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

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

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

                define Floor2_&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 rates */

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

            ods tagsets.ExcelXP options(sheet_interval = 'none'

                                        sheet_name="&temppa Form 2.3-&temps"

                                        absolute_column_width ='10,18,18,18'

                                        autofit_height = 'yes'

                                        ) ;

            %if &temppa ne STATEWIDE %then %do ;

                title1 "Form 2.3 - &temppa Planning Area" ;

            %end ;

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

                title1 "Form 2.3 - &temppa" ;

            %end ;

            title2 "&Tit2" ;

            title3 "Electricity Prices (2010 cents/kWh)" ;

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

                column Year Res_&temps Com_&temps Ind_&temps ;

                define Year / display ;

                define Res_&temps / "Residential" display STYLE(column)={TAGATTR='format:#,###.##'} ;

                define Com_&temps / "Commercial" display STYLE(column)={TAGATTR='format:#,###.##'} ;

                define Ind_&temps / "Industrial" display STYLE(column)={TAGATTR='format:#,###.##'} ;

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

            quit ;

            /* now close ods destination for cec forms */

            ods tagsets.excelxp close ;

            %let j = %eval(&j + 1) ; /* increment to iterate over planning area */

        %end ;

        %let i = %eval(&i + 1) ; /* increment to iterate over scenario */

    %end ;

%mend cecform ;

[asishgautam]

All it appears that I may have made a silly change that was causing problems for me.  If I set the option

autofit_height = 'yes'  then things seem fine.  Please delete this thread.

[KarlK]

There's just too much macro code to sort through to easily tell what's going on, but I did notice one thing. In your OP, you said, "the row separating the two tables has a really large row height". Unless I'm wrong, that's not the case. The row between the tables is Excel row 42 and is fine. The big row is Excel row 39 and that's created by the first "line" statement in your "compute after".

Maybe that will help isolate the problem.

HTH

Karl

[asishgautam]

I may have jumped the gun here.  This is a copy of the program I used a year ago - nothing has changed with the data and I never had this  problem.  Also, I am noticing that the length of my titles can be longer than the size of the table.  Did something change with the excel xp tagset? Everything ran fine last year...

[KarlK]

Don't know, but if it's been a year since you last ran the program, the first thing I'd do is make sure I had the latest version of the ExcelXP tagset. SAS updates the thing frequently. I think the latest is 1.127.

Then, if you could come up with a quick and dirty, SMALL sample program that duplicates your problem, I bet somebody in the forum could zero in on your specific problem in no time.

[asishgautam]

Yep I think I have the latest version of the excelxp tagset:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.127, 09/26/2011). Add

options(doc='help') to the ods statement for more information.

3    ods tagsets.ExcelXP close;

I have attached two small sample input files (Loads.xls and Load_grwth.xls) and a smaller version of the program (Sample_forms.sas).  I also attached the output (PGE_High.xls) I received after running the sample program (Sample_forms.sas).  Here is where I stand so far:

If I use the option autofit_height = 'yes', then the row separating the table from any text (see worksheet labeled "PGE Form 1.1-High") are no longer having unusually large row height.  However, I have noticed another problem - some of the titles now have larger row heights - see

title2 in sheet labeled "PGE Form 1.1b-High".  Another table (not shown) ends up having large row height for title2 and title3.  You can see in the sample program that I tried to mess around with row_heights = '0,0,0,16,0,0,0'  on the tagset option and h=16pt on the title statement to mange row height but I was not having any luck.

edit:

/* I noticed an error in my program and re loaded the program and the resulting output file */

[KarlK]

Hmm . . . Does this apply to you?

44289 - The row height for titles and footnotes might be incorrect when generated with the ExcelXP t...

[asishgautam]

Kind of confused.  The title2 statement that keeps wrapping is the same for all tables - it  does not wrap in the first table - just all the other tables after the first table.