BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
cho16
Obsidian | Level 7

Good Morning all,

 

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

 

Existing:

cho16_0-1675700058605.png

 

 

After adding new column:

cho16_1-1675700058374.png

 

 

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ
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.

View solution in original post

5 REPLIES 5
cho16
Obsidian | Level 7

Good Morning all,

 

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

 

Existing:

cho16_0-1675694545914.png

 

After adding new column:

cho16_1-1675694577566.png

 

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 !

Cynthia_sas
SAS Super FREQ
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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 479 views
  • 0 likes
  • 4 in conversation