Proc report formatting issue

[cho16]

Good Morning all,

I am seeing formatting problems while adding a column to the existing code.

Existing:

After adding new column:

ods _all_ close;
ods excel 
/*    file="%sysget(DSR_DIR)/NonKYC_Delta_CRS_CCMP6754_Response_File_View_&fDate2..xlsx"*/
    file="&DSR_DIR./NonKYC_Delta_CRS_CCMP6754_Response_File_View_&fDate2._upd1.xlsx"
        style=HTMLBlue;
    ods Excel options (
        sheet_name="Summary" 
        start_at="A1" 
        tab_color="gold"
        autofilter="all"
        frozen_headers="on"
        FLOW="HEADERS" 
        FITTOPAGE="OFF"    
        absolute_column_width="6,12,12,26,7,11,7,7,7,9,9,7");

proc report data=Stat_look_week ; 
        columns Week Day Date Workflow_BU LOB Names_Screened Hits Alerts Hit_Rate Alert_Rate 
                shared_acts acts_diff;
        define Week /display 'Week'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={ tagattr='wraptext:no' just=c};
        define Day /display 'Day' 
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={ tagattr='wraptext:yes' just=l};
        define Date /display 'Date' 
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={ tagattr='wraptext:no' just=l};
        define Workflow_BU /display 'Workflow Business Unit'  
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={tagattr='wraptext:no' just=l};
        define LOB /display 'LOB'   
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=c} 
            style(column)={tagattr='wraptext:no' just=c};
        define Names_Screened /Display width=11 'Names Screened'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};
        define Hits /display 'Hits'   
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={tagattr='wraptext:no' just=r};
        define Alerts /display 'Alerts'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};
        define Hit_Rate /display 'Hit Rate'   
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={tagattr='wraptext:no' just=r};
        define Alert_Rate /display 'Alert Rate'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};

        define shared_acts /display 'shared_acts'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};

        define acts_diff /display 'acts_diff'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};

compute LOB;
     if substr(LOB,1,4)='Week' then do;
        call define(_row_,"style","style={background=lightgray}");
        call define(_col_,"style","style={tagattr='wraptext:no' just=r fontstyle=italic fontweight=bold}");
        end;
   endcomp;

run;

 Please let me know if i need to add any additional options to remain same format .

Thanks in advance !

[PaigeMiller]

DUPLICATE THREAD

DO NOT REPLY HERE 
Reply at https://communities.sas.com/t5/SAS-Programming/proc-report-formatting-issue/td-p/857373/jump-to/firs...

[cho16]

Good Morning all,

I am seeing formatting problems while adding a column to the existing code.

Existing:

After adding new column:

ods _all_ close;
ods excel 
/*    file="%sysget(DSR_DIR)/NonKYC_Delta_CRS_CCMP6754_Response_File_View_&fDate2..xlsx"*/
    file="&DSR_DIR./NonKYC_Delta_CRS_CCMP6754_Response_File_View_&fDate2._upd1.xlsx"
        style=HTMLBlue;
    ods Excel options (
        sheet_name="Summary" 
        start_at="A1" 
        tab_color="gold"
        autofilter="all"
        frozen_headers="on"
        FLOW="HEADERS" 
        FITTOPAGE="OFF"    
        absolute_column_width="6,12,12,26,7,11,7,7,7,9,9,7");

proc report data=Stat_look_week ; 
        columns Week Day Date Workflow_BU LOB Names_Screened Hits Alerts Hit_Rate Alert_Rate 
                shared_acts acts_diff;
        define Week /display 'Week'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={ tagattr='wraptext:no' just=c};
        define Day /display 'Day' 
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={ tagattr='wraptext:yes' just=l};
        define Date /display 'Date' 
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={ tagattr='wraptext:no' just=l};
        define Workflow_BU /display 'Workflow Business Unit'  
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={tagattr='wraptext:no' just=l};
        define LOB /display 'LOB'   
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=c} 
            style(column)={tagattr='wraptext:no' just=c};
        define Names_Screened /Display width=11 'Names Screened'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};
        define Hits /display 'Hits'   
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={tagattr='wraptext:no' just=r};
        define Alerts /display 'Alerts'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};
        define Hit_Rate /display 'Hit Rate'   
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l} 
            style(column)={tagattr='wraptext:no' just=r};
        define Alert_Rate /display 'Alert Rate'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};

        define shared_acts /display 'shared_acts'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};

        define acts_diff /display 'acts_diff'
            style(header)={tagattr='wraptext:yes' height = 44 vjust=t just=l}  
            style(column)={tagattr='wraptext:no' just=r};

compute LOB;
     if substr(LOB,1,4)='Week' then do;
        call define(_row_,"style","style={background=lightgray}");
        call define(_col_,"style","style={tagattr='wraptext:no' just=r fontstyle=italic fontweight=bold}");
        end;
   endcomp;

run;

 Please let me know if i need to add any additional options to remain same format .

Thanks in advance !

[Kurt_Bremser]

What do you mean by "same format"?

[Kurt_Bremser]

Please do not double post. It only forces us to merge the threads.

[Cynthia_sas · Accepted solution]

Hi:
I don't know what you mean by the "same format". I'm also not sure why you are trying to over-control the width using absolute_column_width suboption. My recommendation to debug would be to start with "vanilla" ODS EXCEL without any overrides or suboptions -- but with all the variables you want and then slowly add back in the style overrides in PROC REPORT first, one at a time, and see whether you get closer to what you want. And then only when you get the widths and columns as you want, go back to the ODS EXCEL statement and add in things like tab color, autofilter and width, if necessary. You should not need the Start_at suboption because the sheet in ODS EXCEL starts at A1 by default.
Cynthia
PS, it doesn't do much good to post your code if you don't post data with it. Otherwise, for anyone to try to duplicate your issue and possibly tweak your code, they essentially have to guess at the data and the structure and before they can help you they have to make some fake data. It helps everyone help you if you can post some sample data.