proc report

jainmo — Wed, 11 May 2022 04:30:54 GMT

I am creating a proc report and am getting output like this. I don't want gap which is on left side highlighted in yellow. one which in red should all be in one row and like wrap.

I want output like this

Issues-1 CSR introduction Sales Activities and "CBFE Pre-approved Sales Leads including new Credit Card, Credit Limit Increase & COPS (New & Increase)
(Resulting in Monthly Matched Pop Ups)" should be in same row 2

issue 2- in excel spread sheet when I am sheet 2 name more than 2 words like CBFE Preapproved Leads – Weekly - it give me error

Issue 3- I wanted to style it like its in original report like cant format headers for spanning header

my code :

ods listing close;
ods escapechar='^';
options orientation=landscape;
ods excel file="/sasdata/oth/priv/RDBI/TMP/CSR" options(sheet_interval='none'
sheet_name='Weekly &week_num summary');;

proc report data= csr_summary split='/'
style(header)=[backgroundcolor=CX90D9D7 color=black];
column ("Region" REGION)
("Market" DISTRICT )
('Transit' TRANSIT)
('Tranname' TRANNAME )
(
"Total CBFE Leads including Sales (Pre-Approved and
Introduction) & Service Leads"
("Total CBFE Leads Presented (#)" TOT_CL_PRSNT )
("Total CBFE Leads Accepted (#)" TOT_CL_ACCPT)
("Total CBFE Leads Declined (#)" TOT_CL_DECLN )
("Total CBFE Leads Missed (#)" TOT_CL_MISSD)
)

(
"CBFE Pre-approved Sales Leads including new Credit Card, Credit Limit Increase & COPS (New & Increase)
(Resulting in Monthly Matched Pop Ups)"
(
("Total CBFE Pre-app Leads Presented (#)" TOT_PA_PRSNT)
("Total Pre-approved Leads Accepted (#)" TOT_PA_ACCPT)
("Total Pre-approved Leads Declined (#)" TOT_PA_DECLN)
("Total Pre-approved Leads Missed (#)" TOT_PA_MISSD)
)
)

("CSR Introduction Sales Activities"
("CBFE Intro Sales Leads"
("Total CBFE Intro Leads Presented (#)" TOT_CBFE_PRSNT)
("Total CBFE Intro Leads Accepted (#)" TOT_CBFE_ACPT)
)
("CSR Initiated"
("Total Intros CSR initiated through client conversations (#)" TOT_CSR_PRSNT)
)
("Total Intros"
("Total Intros through CBFE & Client Conversations (#)" TOT_INTRO)
)
)
(
"Introductions Closed"
( "Proceed to Sale (#)" PROC_TO_SALE )
("Proceed to Opportunity (#)" PROC_TO_OPPR )
("Total Successful Intros (#)" TOT_SUCCESSFUL )
)
("Other CBFE Leads"
("Service Leads"
( ("Other CBFE Service Leads #" TOT_SERVICE))
)

);

define region / display ' ';
define district / display ' ';
define TRANNAME /DISPLAY ' ';
define transit / display ' ';
define TOT_CL_PRSNT / "A / A=E+I"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_CL_ACCPT / "B"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_CL_DECLN / "C"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_CL_MISSD / "D"
style(header)=[backgroundcolor=CXE8D898 color=black];

define TOT_PA_PRSNT /"E"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_PA_ACCPT / "F"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_PA_DECLN / "G"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_PA_MISSD / "H"
style(header)=[backgroundcolor=CXE8D898 color=black];

define TOT_CBFE_PRSNT/ "I"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_CBFE_ACPT / "J"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_CSR_PRSNT / " K"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_INTRO / "L /L=J+K"
style(header)=[backgroundcolor=CXE8D898 color=black];

define PROC_TO_SALE / "M"
style(header)=[backgroundcolor=CXE8D898 color=black];
define PROC_TO_OPPR /"N"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_SUCCESSFUL / "O/O=M+N"
style(header)=[backgroundcolor=CXE8D898 color=black];
define TOT_SERVICE / " P"
style(header)=[backgroundcolor=CXE8D898 color=black];
RUN;
ods excel options(sheet_interval='output');
ods exclude all;
data _null_;
declare odsout obj();
run;
ods select all;
ods excel options(sheet_interval='none' sheet_name='Preapproved');
proc report data=csr_summary2 split='/';
column
("Region" REGION)
("Market" DISTRICT )
('Transit' TRANSIT)
('Tranname' TRANNAME )
(
"Total CBFE Pre-approved (PA) Sales Leads including Credit Card, Credit Limit Increase & COPS (New & Increase)
(Resulting in Monthly Matched Pop Ups)"
("Total Pre-approved CBFE Sales Leads Presented (#)" TOT_PA_PRSNT )
("Total Accepted (#)" TOT_PA_ACCPT)
("Total Declined (#)" TOT_PA_DECLN )
("Total Missed (#)" TOT_PA_MISSD)
)

(
"CBFE Pre-approved (PA) new Credit Cards (CC) Leads
(Resulting in Monthly Matched Pop Ups)"

("Total PA CC Leads Presented (#)" VIS_PA_PRSNT)
("Total Accepted (#)" VIS_PA_ACCPT)
("Total Declined (#)" VIS_PA_DECLN)
("Total Missed (#)" VIS_PA_MISSD)
)

("CBFE Pre-approved Credit Limit Increases (CLI) Leads
(Resulting in Monthly Matched Pop Ups"

("Total PA CLI Leads Presented (#)" CLI_PA_PRSNT)
("Total Accepted (#)" CLI_PA_ACCPT)
("Total Declined (#)" CLI_PA_DECLN)
("Total Missed (#)" CLI_PA_MISSD)
)

("CBFE Pre-approved COPS (New & Increase) Leads
(Resulting in Monthly Matched Pop Ups"

("Total PA COPS Leads Presented (#)" COP_PA_PRSNT)
("Total Accepted (#)" COP_PA_ACCPT)
("Total Declined (#)" COP_PA_DECLN)
("Total Missed (#)" COP_PA_MISSD)
)

;

define region / display ' ';
define district / display ' ';
define TRANNAME /DISPLAY ' ';
define transit / display ' ';

define TOT_PA_PRSNT / "A / A=E+I+M" ;

define TOT_PA_ACCPT/ "B";
define TOT_PA_DECLN / "C";
define TOT_PA_MISSD / "D";

define VIS_PA_PRSNT /"E";
define VIS_PA_ACCPT / "F";
define VIS_PA_DECLN / "G";
define VIS_PA_MISSD / "H";

define CLI_PA_PRSNT /"I";
define CLI_PA_ACCPT / "J";
define CLI_PA_DECLN / " K";
define CLI_PA_MISSD / "L";

define COP_PA_PRSNT / "M";
define COP_PA_ACCPT /"N";
define COP_PA_DECLN / "O";
define COP_PA_MISSD / " P";
RUN;

Re: proc report

andreas_lds — Wed, 11 May 2022 05:24:07 GMT

issue 2: The length of sheet-names is limited, see https://stackoverflow.com/questions/3681868/is-there-a-limit-on-an-excel-worksheets-name-length

issue 3: Maybe possible to achieve by using Report Writing Interface, but i am not sure that placing the filters is possible.

hint: if you have issues with a report, posting data is necessary so that the community can actually use your code.

Re: proc report

Ksharp — Wed, 11 May 2022 10:49:41 GMT

What I can do is this. Maybe @Cynthia_sas could give you a complete solution.

ods excel file = "c:\temp\temp.xlsx" 
    options(sheet_interval='none' embedded_titles='yes') ; 
title;
ods escapechar = '^'  ;
options missing = 0 orientation=landscape center ; 

proc report data =sashelp.class nowd split = '-' 
                                style(header)={tagattr="wrap:no" background=white 
bordertopwidth=1 bordercolor=black 
borderrightwidth=1 borderrightcolor=black 
color=black just=c textalign=c} 
                                style(report)={borderwidth=1 bordercolor=black just=c}
                                style(column)= {borderwidth=1 bordercolor=black just=r color=black tagattr='format:###,###,###,###,###0'}
                                style(summary)= [just=c textalign=c];
;

column ('^{style[borderbottomwidth=1 borderbottomcolor=white] Total}'  
       ('^{style[borderbottomwidth=1 borderbottomcolor=white bordertopwidth=1 bordertopcolor=white] Category (before change/extension)' 
       ('^{style[bordertopwidth=1 bordertopcolor=white]       Performingtime'
        sex weight height )))
     (' Total'  
       ('^{style[borderbottomwidth=1 borderbottomcolor=white color=white] _'  
('^{style[bordertopwidth=1 bordertopcolor=white] Performingtime' weight=weight2 height=height2))
       ('^{style[borderbottomwidth=1 borderbottomcolor=white color=white] _' 
('^{style[bordertopwidth=1 bordertopcolor=white] Performingtime2' weight=weight3 height=height3))
       ('^{style[borderbottomwidth=1 borderbottomcolor=white color=white] _' 
('^{style[bordertopwidth=1 bordertopcolor=white] Performingtime3' weight=weight4 height=height4))
)
;
define sex / group; 
define weight: / analysis sum ; 
define height: / analysis sum ; 
run ; 

ods excel close;

topic proc report in SAS Programming

proc report

Re: proc report

Re: proc report