proc report placing an extra row...

[asishgautam]

Hi all,

I am using the code below but for some reason, my output, at least for certain "groupings", seem to have an extra row - place take a look at the attached excel file - it may be more clear.

the code is below:

i was also curious as to if there was a way to "freeze" (in excel - this would be the freeze panes option) the top columns.

much thanks.

```````code``````````````````````````

ods tagsets.excelxp

file = 'C:\Documents and Settings\agautam\Desktop\Selfgen\CED 2011\Revised\Data_request\02_02_12_Dvidaver\Out2.xls'

     ;

ods tagsets.ExcelXP options(autofit_height = 'yes'

                            zoom='75'

                            width_fudge='0.75'

                            embedded_titles = 'yes'

                            center_horizontal = 'yes'        

                            ) ;

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

        column Pa Source Year Tech,Fuel,(("Cumulative" Capacity Energy Peak)

                                         ("Annual Addition" Inc_capacity Inc_Energy Inc_Peak)

                                         ) ;

        define Pa / "Planning Area" group ;

        define Source / group ;

        define Year / order ;

        define Tech / across ;

        define Fuel / across  ;

        define Capacity / display STYLE(column)={TAGATTR='format:#,###.##'} ;

        define Energy / display STYLE(column)={TAGATTR='format:#,###.##'} ;

        define Peak / display STYLE(column)={TAGATTR='format:#,###.##'} ;

        define Inc_capacity / "Capacity" display STYLE(column)={TAGATTR='format:#,###.##'} ;

        define Inc_Energy / "Energy" display STYLE(column)={TAGATTR='format:#,###.##'} ;

        define Inc_Peak / "Peak" display STYLE(column)={TAGATTR='format:#,###.##'} ;

    run ;

ods tagsets.excelxp close ;

[Cynthia_sas · Accepted solution]

Hi:

  As long as the usage of YEAR is ORDER (instead of GROUP), then PROC REPORT will show you each observation for YEAR as a separate row (but will blank out the repetitious display of YEAR on the second, third and fourth rows, etc).

  Look at the difference for the rows for AGE in the output below when the usage is ORDER versus GROUP.

  Also, if you use doc='Help' as a sub-option, you can read in the SAS log about FROZEN_HEADERS and how to freeze row headers.

cynthia

ods tagsets.excelxp file='c:\temp\order_vs_group.xls'

    options(doc='Help' frozen_headers='yes')

    style=sasweb;

proc report data=sashelp.class nowd;

  column age sex,height;

  define age / order;

  define sex/ across;

  define height / mean;

run;

proc report data=sashelp.class nowd;

  column age sex,height;

  define age / group;

  define sex/ across;

  define height / mean;

run;

ods _all_ close;