The SAS Output Delivery System and reporting techniques

Formating column header in proc report

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Formating column header in proc report

I'm trying to suppress the frequency count header without leaving a blank row and I want lines separating the error types from the department names and totals.  Below is the current header, future header and my code. 

Thanks for the help.  This is my first proc report and this is the last thing I'm stuck on. 

Charlotte

Current Header:


 


 

 

Error Types


 

 


 

 


 

 

Error Free


 

 

Attending


 

 

Event Type


 

 

Provider


 

 

RTT Process Provider


 

 

Unknown Provider For Consult


 

 


 

 

Department Name


 

 


 

 


 

 


 

 


 

 


 

 


 

 

Total Appointments


 

Future Header:


 


 

 

Error Types


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 


 

 

Department Name


 

 

Error Free


 

 

Attending


 

 

Event Type


 

 

Provider


 

 

RTT Process Provider


 

 

Unknown Provider For Consult


 

 

Total Appointments


 

Code:

report data=qa10 nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

column dept_name error_type,count total_appt; 

define dept_name / group width=25 "Department Name" ;

     /*format for alternating row highlight*/

     compute dept_name;

          1;

          if (mod(count,2)) then do;

          CALL DEFINE(_ROW_, "STYLE",

          "STYLE=[BACKGROUND=#DCDCDC]");

          end;

     endcomp;

define error_type /across format=etypfmtb. "Error Types" order=internal ;/*orders the error types as required*/

define count /analysis ""; /*must be analysis variable so it will summarize at bottom*/

define total_appt / computed "Total Appointments" ;

     compute total_appt ;

                    total_appt=sum(_C2_, _C3_, _C4_, _C5_, _C6_, _C7_);

     endcomp;

after / summarize skip dol dul;

     compute after;

          dept_name="Total:";
     endcomp;

run;

quit;


Accepted Solutions
Solution
‎02-17-2015 10:13 PM
SAS Super FREQ
Posts: 8,861

Re: Formating column header in proc report

Posted in reply to charlotteking

Hi, without data it is impossible to make up data that makes sense. I do not understand how your current Error Types header is being placed as you show, that is WAY too much space for any destination that I know. And your code is missing pieces. You don't have the keyword PROC or RBREAK. Also your SKIP, DOL and DUL options will be ignored by any destination except listing, so your use of style overrides is inconsistent with LISTING only options. You did not show ALL your code. What destination do you want? Does your code run without errors? The 1; should be causing you a syntax error. Is this HTML, PDF, RTF or LISTING  output that you want? Again, it is impossible to tell, since you did not post your full proc report code, your destination code or any data.
    

  You might try specifying

column dept_name count,error_type total_appt; 

so that the "blank" rows will be suppressed. PROC REPORT writes 1 row header at a time. So at the point in time when PROC REPORT is writing the header for the ACROSS variable, it does not have visibility of the column header for anything that is going to fit on subsequent header rows. The only way that PROC REPORT collapses header rows is when an entire row is blanks. I suspect what's happening is that the header for Department Name and Total Appointments is making it impossible for the blanks created by your header for count to go away.

  If you compare these 3 much simpler PROC REPORT steps, you will see what I mean.
      

Cynthia

proc sort data=sashelp.shoes out=shoes;

  where region in ('Asia', 'Pacific', 'Canada');

  by region product;

run;

 

ods html file='c:\temp\reporttest.html';

proc report data=shoes nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

  title '1) show headers specified incorrectly';

  column region product,sales sales=totsales; 

  define region / group width=25 "Department Name" ;

  define product /across   "Error Types" order=internal ;/*orders the error types as required*/

  define sales /sum ""; /*must be analysis variable so it will summarize at bottom*/

  define totsales / sum "Total Appointments" ;

  rbreak after / summarize;

       /*format for alternating row highlight*/

  compute region;

   rowcnt+1;

      if (mod(rowcnt,2)) then do;

          CALL DEFINE(_ROW_, "STYLE","STYLE=[BACKGROUND=#DCDCDC]");

      end;

  endcomp;

  

  compute after;

      region="Total:";

  endcomp;

run;

proc report data=shoes nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

  title '2) show headers specified  correctly to suppress blank row';

  column region sales,product sales=totsales; 

  define region / group width=25 "Department Name" ;

  define product /across   "Error Types" order=internal ;/*orders the error types as required*/

  define sales /sum ""; /*must be analysis variable so it will summarize at bottom*/

  define totsales / sum "Total Appointments" ;

  rbreak after / summarize;

       /*format for alternating row highlight*/

  compute region;

   rowcnt+1;

      if (mod(rowcnt,2)) then do;

          CALL DEFINE(_ROW_, "STYLE","STYLE=[BACKGROUND=#DCDCDC]");

      end;

  endcomp;

  

  compute after;

      region="Total:";

  endcomp;

run;

     

proc report data=shoes nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

  title '3) Alternate Method Using Spanning Headers';

  column ("Department Name" region) product,sales ("Total Appointments" sales=totsales); 

  define region / group width=25 " " ;

  define product /across   "Error Types" order=internal ;/*orders the error types as required*/

  define sales /sum ""; /*must be analysis variable so it will summarize at bottom*/

  define totsales / sum " " ;

  rbreak after / summarize;

       /*format for alternating row highlight*/

  compute region;

   rowcnt+1;

      if (mod(rowcnt,2)) then do;

          CALL DEFINE(_ROW_, "STYLE","STYLE=[BACKGROUND=#DCDCDC]");

      end;

  endcomp;

   

  compute after;

      region="Total:";

  endcomp;

run;

ods html close;


report_I_think_you_want.png

View solution in original post


All Replies
Solution
‎02-17-2015 10:13 PM
SAS Super FREQ
Posts: 8,861

Re: Formating column header in proc report

Posted in reply to charlotteking

Hi, without data it is impossible to make up data that makes sense. I do not understand how your current Error Types header is being placed as you show, that is WAY too much space for any destination that I know. And your code is missing pieces. You don't have the keyword PROC or RBREAK. Also your SKIP, DOL and DUL options will be ignored by any destination except listing, so your use of style overrides is inconsistent with LISTING only options. You did not show ALL your code. What destination do you want? Does your code run without errors? The 1; should be causing you a syntax error. Is this HTML, PDF, RTF or LISTING  output that you want? Again, it is impossible to tell, since you did not post your full proc report code, your destination code or any data.
    

  You might try specifying

column dept_name count,error_type total_appt; 

so that the "blank" rows will be suppressed. PROC REPORT writes 1 row header at a time. So at the point in time when PROC REPORT is writing the header for the ACROSS variable, it does not have visibility of the column header for anything that is going to fit on subsequent header rows. The only way that PROC REPORT collapses header rows is when an entire row is blanks. I suspect what's happening is that the header for Department Name and Total Appointments is making it impossible for the blanks created by your header for count to go away.

  If you compare these 3 much simpler PROC REPORT steps, you will see what I mean.
      

Cynthia

proc sort data=sashelp.shoes out=shoes;

  where region in ('Asia', 'Pacific', 'Canada');

  by region product;

run;

 

ods html file='c:\temp\reporttest.html';

proc report data=shoes nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

  title '1) show headers specified incorrectly';

  column region product,sales sales=totsales; 

  define region / group width=25 "Department Name" ;

  define product /across   "Error Types" order=internal ;/*orders the error types as required*/

  define sales /sum ""; /*must be analysis variable so it will summarize at bottom*/

  define totsales / sum "Total Appointments" ;

  rbreak after / summarize;

       /*format for alternating row highlight*/

  compute region;

   rowcnt+1;

      if (mod(rowcnt,2)) then do;

          CALL DEFINE(_ROW_, "STYLE","STYLE=[BACKGROUND=#DCDCDC]");

      end;

  endcomp;

  

  compute after;

      region="Total:";

  endcomp;

run;

proc report data=shoes nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

  title '2) show headers specified  correctly to suppress blank row';

  column region sales,product sales=totsales; 

  define region / group width=25 "Department Name" ;

  define product /across   "Error Types" order=internal ;/*orders the error types as required*/

  define sales /sum ""; /*must be analysis variable so it will summarize at bottom*/

  define totsales / sum "Total Appointments" ;

  rbreak after / summarize;

       /*format for alternating row highlight*/

  compute region;

   rowcnt+1;

      if (mod(rowcnt,2)) then do;

          CALL DEFINE(_ROW_, "STYLE","STYLE=[BACKGROUND=#DCDCDC]");

      end;

  endcomp;

  

  compute after;

      region="Total:";

  endcomp;

run;

     

proc report data=shoes nowd

     style(report)={rules=groups font_size=10}

     style(header)=header{background=white rules=groups};

  title '3) Alternate Method Using Spanning Headers';

  column ("Department Name" region) product,sales ("Total Appointments" sales=totsales); 

  define region / group width=25 " " ;

  define product /across   "Error Types" order=internal ;/*orders the error types as required*/

  define sales /sum ""; /*must be analysis variable so it will summarize at bottom*/

  define totsales / sum " " ;

  rbreak after / summarize;

       /*format for alternating row highlight*/

  compute region;

   rowcnt+1;

      if (mod(rowcnt,2)) then do;

          CALL DEFINE(_ROW_, "STYLE","STYLE=[BACKGROUND=#DCDCDC]");

      end;

  endcomp;

   

  compute after;

      region="Total:";

  endcomp;

run;

ods html close;


report_I_think_you_want.png
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 724 views
  • 0 likes
  • 2 in conversation