SAS Excell Report - Section Column Breaks

[Jyuen204]

Say i want to ave my excel repport with a separation  so there is a space/column between Nam and Sex and between Group 1 and Group 2. How would I code this to have that vertical break?

proc sort data=sashelp.class out=class;

by sex;

run;

%macro report_test( inTable );

proc report data=&inTable split='#' spanrows

style(report)=[outputwidth = 100% cellspacing=0 cellpadding=2 frame = hsides rules=groups font=(Arial, 8pt, bold)]

style(header)=[foreground=white background=CX0066a4/*00669A*/ font=(Arial, 9pt, bold) borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white NOBREAKSPACE=ON ]

style(column)=[font=(Arial, 8pt) foreground=black background=CXFFFFFF  VJUST=C bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ];

Columns

NAME

("Group 1"

SEX

AGE

)

("Group 2"

HEIGHT

WEIGHT

)

;

    Define NAME / order=data 'NAME' style(column)= {background=GRAYE6};

Define SEX / order=data 'SEX';

Define AGE / CENTER 'AGE' format = comma12.;

    Define HEIGHT / CENTER 'HEIGHT' format = comma12.;

Define WEIGHT / CENTER 'WEIGHT' format = comma12.;

/*compute before CATEGORY / style=[font=(Arial, 9pt, bold) just=left background=white BorderbottomColor=cx000000 borderbottomwidth=0.05]; Line '' Dimension $32.; endcomp;*/

/*compute P3M ;call define(_COL_,'style','style=[borderleftwidth = 0.5 borderleftcolor=black ]');endcomp;*/

Compute NAME; endcomp;

Compute SEX; endcomp;

Compute AGE; endcomp;

Compute HEIGHT; endcomp;

Compute WEIGHT; endcomp;

run;

%mend report_test;

ODS LISTING CLOSE;

/*ods tagsets.ExcelXP path= "&report_dir" file= 'Bell MTS - Weekly Report.xls' Style=Printer;*/

ods tagsets.ExcelXP style=sasweb path= "&OUTPUT_PATH" file= "&ENAME" Style=Printer;

ods tagsets.ExcelXP options(sheet_interval="none" sheet_name='Weekly Summary Report' AUTOFIT_HEIGHT="yes"  absolute_column_width='8,6,6,6,6');

%report_test(CLASS);

[PaigeMiller · Accepted solution]

If I want a blank column somewhere in a PROC REPORT output, I have created a fake variable (which I named GAP):

proc format;
    value gapf .,low-high=' ';
run;
proc report;
    columns column1 column2 gap column3 column4 gap column5 column6;
    define gap / ' ' style(column)={cellwidth=.03in background=cxe7e7e7 bordertopwidth=0 borderbottomwidth=0} 
        format=gapf. style(header)={color=cxe7e7e7 bordertopwidth=0 borderbottomwidth=0};
run;

Note, GAP should NOT be a variable in your data set. You can use the fake variable named GAP as many times as you want, you can specify the width, the background color, and other appearance options. Also, although you ask about Excel, this method works for any output destination (PDF, RTF, HTML, etc.)