topic proc report formatting issue in SAS Procedures

Proc report formatting issue

cho16 — Mon, 06 Feb 2023 16:14:30 GMT

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 !

proc report formatting issue

cho16 — Mon, 06 Feb 2023 14:44:48 GMT

Good Morning all,

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

Existing:

After adding new column:

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

run;

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

Thanks in advance !

Re: Proc report formatting issue

PaigeMiller — Mon, 06 Feb 2023 17:16:28 GMT

DUPLICATE THREAD

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

Re: proc report formatting issue

Kurt_Bremser — Mon, 06 Feb 2023 17:22:58 GMT

What do you mean by "same format"?

Re: proc report formatting issue

Kurt_Bremser — Mon, 06 Feb 2023 17:25:38 GMT

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

Re: proc report formatting issue

Cynthia_sas — Mon, 06 Feb 2023 19:13:47 GMT

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.