BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
asishgautam
Calcite | Level 5

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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;

View solution in original post

1 REPLY 1
Cynthia_sas
SAS Super FREQ

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1275 views
  • 0 likes
  • 2 in conversation