The SAS Output Delivery System and reporting techniques

blank placeholders within subheaders and blank spaces between columns

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 139
Accepted Solution

blank placeholders within subheaders and blank spaces between columns

Hi all,

I have two small technical details to work out in which I hope this is feasible-

1) A blank placeholder within a spanned header-

Within proc report I have:

columns ('PROCESS' ('Placeholder'  ComplianceHHa,ComplianceHH complianceISOa,complianceISO DOT_1000a,DOT_1000 complianceATPa,complianceATP))

The issue is that if I use ' ' then the left boundary line does not appear and it looks odd-is there a way to get this to be blank...or insert a URL link even better?

2) So in total there is-

columns ('PROCESS' ('Placeholder'  ComplianceHHa,ComplianceHH complianceISOa,complianceISO DOT_1000a,DOT_1000 complianceATPa,complianceATP))  ('OUTCOME'('1 Month' numa,num /*lastmoa,lastmo*/)  ('3 Months' lastmoa3,lastmo3)  ('12 Months' numa12,num12 lastmoa12,lastmo12)) ;

Is there a way to create an small space between the two sections-

I have attempted to use an extra column called emp but it leaves "blank squares" with borders.

Any help much appreciated.

Lawrence


Accepted Solutions
Solution
‎08-01-2012 05:02 PM
SAS Super FREQ
Posts: 8,862

Re: blank placeholders within subheaders and blank spaces between columns

Hi:

  Border lines are drawn/rendered differently for different destinations. What is your destination of interest????

  Also, it would be useful if you could post your DEFINE statements and more of your code. Everything on the report looks like an ACROSS item, so it will be hard to use CALL DEFINE on just one cell to change the border style of that cell-- you'd have to use absolute column numbers to change the style of just 1 ACROSS cell. And, if your destination of interest is PDF, sometimes it is hard to touch only the right border of a cell, for example, without having an impact on the other borders of the cell. As I said, different destinations treat border lines differently.

And, while CALL DEFINE might be able to touch the data cell area and the border of a data cell, it does not touch the header cell area. So if you wanted your divider line to appear up in the header area of the report, then you're back to having an "empty" or fake column as a divider.

  Or you might consider making 2 separate tables and if you want RTF or PDF, then use COLUMNS=2 to put the 2 tables side by side instead of trying to put an artificial divider down the middle. Some test code for COLUMNS=2 is below.

  Or, if you want to pursue the border change possibility or the fake empty column possibility, then you might want to work with Tech Support. Your code and destination and other things in the program (ODS LAYOUT?) would have an impact on the solution.

cynthia

ods pdf file='c:\temp\use_col.pdf' columns=2 notoc;

ods rtf file='c:\temp\use_col.rtf' columns=2;

ods escapechar='~';

   

title; footnote;

proc report data=sashelp.prdsale nowd ;

  where country in ('CANADA', 'U.S.A.') and

        product in ('CHAIR', 'DESK', 'TABLE');

  columns ('PROCESS' (country, region,actual)  );

  define country / across;

  define region / across;

  define actual / sum;

run;

 

** disappear the word "Placeholder" using style overrides;

proc report data=sashelp.prdsale nowd ;

  where country in ('CANADA', 'U.S.A.') and

        product in ('CHAIR', 'DESK', 'TABLE');

  columns ("~{style[foreground=cxbbbbbb]Placeholder}" ('OUTCOME'('1 Month' product,predict ) ) );

  define product / across ;

  define predict / sum;

run;

ods _all_ close;

View solution in original post


All Replies
Solution
‎08-01-2012 05:02 PM
SAS Super FREQ
Posts: 8,862

Re: blank placeholders within subheaders and blank spaces between columns

Hi:

  Border lines are drawn/rendered differently for different destinations. What is your destination of interest????

  Also, it would be useful if you could post your DEFINE statements and more of your code. Everything on the report looks like an ACROSS item, so it will be hard to use CALL DEFINE on just one cell to change the border style of that cell-- you'd have to use absolute column numbers to change the style of just 1 ACROSS cell. And, if your destination of interest is PDF, sometimes it is hard to touch only the right border of a cell, for example, without having an impact on the other borders of the cell. As I said, different destinations treat border lines differently.

And, while CALL DEFINE might be able to touch the data cell area and the border of a data cell, it does not touch the header cell area. So if you wanted your divider line to appear up in the header area of the report, then you're back to having an "empty" or fake column as a divider.

  Or you might consider making 2 separate tables and if you want RTF or PDF, then use COLUMNS=2 to put the 2 tables side by side instead of trying to put an artificial divider down the middle. Some test code for COLUMNS=2 is below.

  Or, if you want to pursue the border change possibility or the fake empty column possibility, then you might want to work with Tech Support. Your code and destination and other things in the program (ODS LAYOUT?) would have an impact on the solution.

cynthia

ods pdf file='c:\temp\use_col.pdf' columns=2 notoc;

ods rtf file='c:\temp\use_col.rtf' columns=2;

ods escapechar='~';

   

title; footnote;

proc report data=sashelp.prdsale nowd ;

  where country in ('CANADA', 'U.S.A.') and

        product in ('CHAIR', 'DESK', 'TABLE');

  columns ('PROCESS' (country, region,actual)  );

  define country / across;

  define region / across;

  define actual / sum;

run;

 

** disappear the word "Placeholder" using style overrides;

proc report data=sashelp.prdsale nowd ;

  where country in ('CANADA', 'U.S.A.') and

        product in ('CHAIR', 'DESK', 'TABLE');

  columns ("~{style[foreground=cxbbbbbb]Placeholder}" ('OUTCOME'('1 Month' product,predict ) ) );

  define product / across ;

  define predict / sum;

run;

ods _all_ close;

Frequent Contributor
Frequent Contributor
Posts: 139

Re: blank placeholders within subheaders and blank spaces between columns

Posted in reply to Cynthia_sas

Cynthia;

Thanks for the tip on the "placeholder" issue-I knew I was close, but was off on the syntax.

I do like your idea on splitting the report-it may get a little complicated as I have another "report" in there in that I had used Proc Report to fake a report header at the top that connects through to other pages in the report.

Duly noted on attempting to muck with the gridlines. I will leave as is.

Code is below and is only for one page (of 6) of entire report.

Thanks for your assistance once again!

Lawrence

ods escapechar="\";

ods listing gpath="P:\" ;

ods graphics on/reset=all antialias=on width=9in height=5in border=no;

options  nodate nonumber center leftmargin=.25in rightmargin=.25in topmargin=.05in bottommargin=.15in

orientation=landscape;

symbol v = dot ;

ods pdf file='P:\xxx JULY 2012a.pdf'  style=Styles.XCL dpi=300;

ods pdf anchor='menu';

proc report data=b nowd spanrows NOCENTER STYLE(column)={background=white borderbottomcolor=white borderleftcolor=white bordertopcolor=white borderrightcolor=white fontsize=0pt cellpadding=0pt cellspacing=0pt}

STYLE(header)={ cellspacing=0pt cellpadding=0pt color=white background=blue borderbottomcolor=black borderbottomwidth=1pt  borderleftcolor=black borderleftwidth=1pt bordertopcolor=black bordertopwidth=1pt borderrightcolor=black borderrightwidth=1pt};

columns a b c d e;

define a/'Report Menu' STYLE(Header)= {URL="#menu"};

define b /'Scorecard' STYLE(Header)= {URL="#score"};

define c /'Trend Charts' STYLE(Header)= {URL="#trend"};

define d /'Control Charts' STYLE(Header)= {URL="#control"};

define e /'Definitions' STYLE(Header)= {URL="#definitions"};

run;

ods pdf text="\S={leftmargin=1.5in preimage='T:\Hospital Reporting Team\Inp Quality Analytic Team\HA-CDI\HA-CDI Dashboard\TEMPLATE\xxx.bmp'  font_size=7 font_weight=bold}  xxx";

ods pdf startpage=no;

proc report data=measuresIII  headline headskip nowd spanrows style(report)={font_size=6pt cellpadding=1pt cellspacing=1pt}

style(column) = {font = ("arial",6.0pt) just=center}

;;

columns fac_id color colorstar pctchange COLORSCHEME

('PROCESS' ("\{style[foreground=white]Placeholder}"  ComplianceHHa,ComplianceHH complianceISOa,complianceISO DOT_1000a,DOT_1000 complianceATPa,complianceATP)) _emp

('OUTCOME'('1 Month' numa,num /*lastmoa,lastmo*/)  ('3 Months' lastmoa3,lastmo3)  ('12 Months' numa12,num12 lastmoa12,lastmo12)) ;

define _emp/' ' style={width=.3% background=white color=white};

DEFINE colorstar/' '  style(header)={background=white} noprint analysis;

DEFINE color/' ' noprint analysis;

DEFINE FAC_ID/group 'STUDY PERIOD---->'  order format=$code. style(header)={background=white}

style(column)={LINKCOLOR=black background=white color=black};

DEFINE numa/ACROSS ' '  style(header)={background=white};

DEFINE num/"MAY12" center  style(header)={background=yellow};

DEFINE lastmoa/ACROSS ' '  style(header)={background=white};

DEFINE lastmo/ "MAY12" center   style(header)={background=yellow};

DEFINE numa3/ACROSS ' '  style(header)={background=white};

DEFINE num3/"MAR12/to/MAY12" center   style(header)={background=yellow};

DEFINE lastmoa3/ACROSS ' ' style(header)={background=white};

DEFINE lastmo3/"MAR12/to/MAY12*" center style(header)={background=yellow};

DEFINE numa12 /ACROSS ' ' style(header)={background=white};

DEFINE num12/ "JUN11/to/MAY12" center  style(header)={background=yellow} ;

DEFINE lastmoa12/Across ' ' style(header)={background=white};

DEFINE lastmo12/"JUN11/to/MAY12" center   style(header)={background=yellow};

DEFINE pctchange/' ' noprint analysis; 

DEFINE ComplianceHHa /across ' ' style(header)={background=white};

DEFINE ComplianceHH /"Q2 2012" style(header)={background=yellow} format=percent10. style(column)=[background=sigf. color=sigg.];

DEFINE complianceISOa /across ' ' style(header)={background=white};

DEFINE complianceISO /"&DATEMACRO" style(header)={background=yellow} format=percent10. style(column)=[background=sigb. color=sigc.];

DEFINE DOT_1000a /across ' ' style(header)={background=white};

DEFINE DOT_1000 /"&DATEMACRO" style(header)={background=yellow};

DEFINE complianceATPa /across ' ' style(header)={background=white};

DEFINE complianceATP /"&DATEMACRO" style(header)={background=yellow} format=percent10. style(column)={background=sigd. color=sige.};

DEFINE colorscheme/ analysis noprint;

compute COMPLIANCEATP;

       if colorscheme.sum=0 then call define(_COL_,'style','style={background=lightgray color=lightgray}');

       /*else if FACILITY in ('ZZC','ZTO')  then call define(_ROW_,'style','style={background=lightgray color=black}');

       else if FACILITY in ('ZTO')  then call define(_ROW_,'style','style={background=lightgray color=black }');*/

endcomp;

compute lastmo3;

if fac_id not in ('ZZC','AAZ','ZTO') and pctchange.sum=1 then call define(_COL_,'style','style={preimage="T:\Hospital Reporting Team\Inp Quality Analytic Team\HA-CDI\HA-CDI Dashboard\FORMATS\green_arrow.bmp"}');

if fac_id not in ('ZZC','AAZ','ZTO') and pctchange.sum=0 then call define(_COL_,'style','style={preimage="T:\Hospital Reporting Team\Inp Quality Analytic Team\HA-CDI\HA-CDI Dashboard\FORMATS\red_arrow.bmp"}');

endcomp;

compute num;

if colorstar.sum=1 then call define(_COL_,'style','style={background=white preimage="T:\Hospital Reporting Team\Inp Quality Analytic Team\HA-CDI\HA-CDI Dashboard\FORMATS\greenstar.bmp"}');

if fac_id not in ('ZZC','ZTO') and colorstar.sum=0 then call define(_COL_,'style','style={background=white preimage="T:\Hospital Reporting Team\Inp Quality Analytic Team\HA-CDI\HA-CDI Dashboard\FORMATS\blank.bmp"}');

if fac_id in ('ZZC','ZTO')  then call define(_COL_,'style','style={color=lightgray}');

*if fac_id in ('AAZ')  then call define(_COL_,'style','style={color=white}');

endcomp;

compute fac_id;

       /*urlstring=compress("#"||fac_id);*/

     /* IF fac_id NOT IN ('AAZ','ZZC') then call define(_COL_,'url',urlstring) ;*/

      if fac_id in ('ZTO')  then call define(_ROW_,'style','style={background=lightgray color=black}');

       if fac_id in ('ZTO')  then call define(_COL_,'style','style={background=lightgray color=black}');

      if fac_id in ('ZZC')  then call define(_ROW_,'style','style={background=lightgray color=black}');

      if fac_id in ('AAZ')  then call define(_ROW_,'style','style={background=white color=black  FONTWEIGHT=bold}');

      *IF fac_id in ('ZTO') then line ' ';

endcomp;

compute lastmo12;

if fac_id not in ('ZZC','AAZ','ZTO') and color.sum=1  then call define(_COL_,'style','style={background=blue color=white}');

if fac_id not in ('ZZC','AAZ','ZTO') and color.sum=0  then call define(_COL_,'style','style={background=red color=black}');

endcomp;

compute _emp;

if fac_id in ('ZTO','ZZC','AAZ')  then call define(_COL_,'style','style={background=lightgray color=lightgray}');

endcomp;

run;

ods pdf close;

SAS Super FREQ
Posts: 8,862

Re: blank placeholders within subheaders and blank spaces between columns

Hi:

  I was positive that you did not have just ACROSS variables. Now I see that you have quite a few variables BEFORE your first ACROSS item (fac_id color colorstar pctchange COLORSCHEME) and even if they are NOPRINT, they would also impact the absolute column numbers. I see that you are using COMPUTE blocks and are avoiding the absolute column number issue by using _COL_ and _ROW_ which will work as long as you do NOT need to make a change to any single ACROSS item under  specific grouped value.

  And I had some other commends on things I noticed in your code. For example, you have this:

compute fac_id;

   <...more code...>

      *IF fac_id in ('ZTO') then line ' ';

endcomp;

  I suspect you commented out the IF statement because it wasn't working.  A LINE statement can only be executed or written at "break" points. So you cannot write a LINE in the COMPUTE block for FAC_ID and you can't write a LINE statement conditionally (PROC REPORT always wants to execute the LINE statement.) If you had something like this:

  COMPUTE BEFORE fac_id;

     holdfac = fac_id;

  ENDCOMP:

  COMPUTE AFTER fac_id;

      if holdfac = 'ZTO' then do;

        writethis = 'This is something to write for ZTO';

        lg = 50;

      end;

      else do;

        writethis = ' ';

        lg=0;

      end;

      line writethis $varying. lg;

  ENDCOMP;

In the COMPUTE BEFORE, this code snippet "grabs" the value of FAC_ID -- PROC REPORT does not have a PDV, like the DATA step, so if you want to save a value and use it at the break point, sometimes you have to put the value into a temporary report variable (like HOLDFAC). Temporary report variables are automatically retained, so that's a handy thing to know about PROC REPORT....and you do NOT want to put HOLDFAC in a COLUMN statement because then that would "break" the automatic retain.

Then, at the break, if the value of HOLDFAC is ZTO, then you set a variable to what you want to write. Normally, you cannot execute a LINE statement conditionally -- so there's no point in putting a LINE statement inside an IF condition. The only thing you  can do is make the length 0 for when you want to suppress the LINE and make the length greater than 0 if you want to write the line. $VARYING format will take a length variable, so that's why LG is set to 0 for the ELSE condition.

Also, I notice that you have an ODS GRAPHICS statement and a SYMBOL statement. Just a word of caution. If you have SAS/GRAPH (like GPLOT or GCHART in the rest of your report, then the SYMBOL statement will be used (and the ODS GRAPHICS statement will be ignored by SAS/GRAPH). If, however, you are using ODS GRAPHICS (such as SGPLOT or SGSCATTER, etc), then the SYMBOL statement will be ignored. GOPTIONS and SYMBOL, AXIS, LEGEND, PATTERN are how you control "classic" SAS/GRAPH procedures; ODS GRAPHICS statement and style templates are how you control ODS GRAPHICS procedures and ODS GRAPHICS-enabled Stat procedures.

My .02,

cynthia

Frequent Contributor
Frequent Contributor
Posts: 139

Re: blank placeholders within subheaders and blank spaces between columns

Posted in reply to Cynthia_sas

Cynthia;

Well I have to say that you beat me to the punch on the next question!

I was just working my way down the chain.

Thanks for looking at the code and helping out with it. That snippet of code is quite helpful to know.

Yes, this is a fairly complex report (in my mind) to emulate the Excel template.

I have to say that your assistance (and others at SAS) keeps me going on these projects!

Thanks again-

Lawrence

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 523 views
  • 3 likes
  • 2 in conversation