The SAS Output Delivery System and reporting techniques

Proc Report - Border color problem

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Proc Report - Border color problem

There is  code in the DEFINE that sets the first two columns with top and bottom borders to white.  There is extra thick border when the value of ORIGIN which is exactly what I want, as the extra thick border makes it easier to read.  However, there are two issues.

ISSUE 1:  The very last row in the report shows as above with no border for the last two columns.  I know it's due to setting the border to white in the define statement.  I would like for the BORDER of the last row of the report to be black.

ISSUE 2:  No matter what I try (see compute MAKE; statement), I can't get a border above the second column when that value changes.  So, I would like a black border when there is a new MAKE.  So, there should be a top border for the first instance of Scion, MINI, Porsche and Jeep.  Here is the error msg:

ERROR: Invalid column specification in CALL DEFINE.

NOTE:  Argument 1 to function DEFINE at line 1 column 26 is invalid.

I can live with issue 2, but issue 1 needs to be resolved.

I attached the code and results.

Thanks so much for your time and comments.

Linda

Attachment

Accepted Solutions
Solution
‎05-18-2012 12:52 AM
SAS Super FREQ
Posts: 8,743

Re: Proc Report - Border color problem


Hi,
  
I would recommend that instead of trying to "overcontrol" the border lines, explore what you can get automatically by using the SPANROWS option, which is designed to do what you want to do (remove the interior lines when a group item spans multiple rows). Then if you need to control border lines if a table breaks across pages, you can investigate other techniques.

 

The program below is what I used to illustrate the alternate approach using SPANROWS. Instead of fiddling with border top and border bottom, I think using the LINE statement in a COMPUTE AFTER block is probably the most reliable way to do something at the end of the group for ORIGIN. You can make the LINE foreground and background the same color...RTF won't let you go too small on fontsize or cellheight, but I made the color of the LINE output gray so you could see where the thicker division would occur.

BTW, the reason you were getting an error message on your CALL DEFINE for invalid argument was because the only unquoted values for the 1st argument  that are allowed are _COL_ and _ROW_ -- any item name must be a quoted value. If you did have ACROSS items (which you didn't), then you might need absolute column numbers, and if you did use absolute column numbers, then would have to be quoted in the CALL DEFINE statement ('_c2_','style','style=...'). But for your case, I think the simple _COL_ would have worked. But since SPANROWS wll do what you want, it might be worthwhile to investigate that first.

cynthia


OPTIONS ORIENTATION=LANDSCAPE NODATE NONUMBER center papersize=letter
TOPMARGIN=0.5 CM BOTTOMMARGIN=0.5 cm leftmargin=.5 cm  rightmargin=.75 cm ;
ODS LISTING CLOSE;

ods rtf file="c:\temp\carsSample_revised2.rtf" startpage=No 

Title1;

footnote j=l font='Arial Narrow' h=6.5pt

         "^S={protectspecialchars=off "

         "pretext='\brdrt\brdrs\brdrw1 '}This is a footnote with a line.";

** some of your options, like headline, headskip and spacing;
** are ignored for ODS. They are LISTING only options;
** Also, a fontsize of .1, without any unit does not make sense to me,;
** so I changed them to explicit fontsize of 6.5pt -- which is pretty small;
** And since you have all character variables, changing the summary line,;
** does not make sense either, but I left it in.;
** I think it's probably more reliable to put a divider line after each ORIGIN;
** by using the LINE statement in a COMPUTE AFTER block, than to fiddle;
** with the bordertop and border bottom controls.;

proc report data=sashelp.cars nowindows  split='*' spanrows
  style(report)=[ cellspacing=0 bordercolor=black fontsize=6.5pt
        width=9.75 in font_face='Arial Narrow' cellpadding=2]
  style(header)=[ color=Black fontsize=6.5pt font_face='Arial Narrow' ]
  style(column)=[ color=black fontsize=6.5pt font_face='Arial Narrow' ]
  style(summary)=[ color=cx3e3d73 backgroundcolor=cxaeadd9 fontsize=6.5pt textalign=r font_face='Arial Narrow'];
  where make in ('Scion', 'Land Rover', 'Isuzu','MINI', 'Porsche', 'Jeep', 'Hummer');
  column Origin Make  drivetrain type ;
   define Origin  /  group   'Origin';  
   define Make  /  group   'Make'   ;
   define drivetrain / group  "Drive Train" ;  
   define Type / group   'Type'   ;
   break after origin /;
   compute after origin / style={color=cxcccccc background=cxcccccc
                                 fontsize=.1pt cellheight=.1pt};
     line ' ';
   endcomp;
run;
ods rtf close;

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,743

Re: Proc Report - Border color problem

Hi:

  Do you mean that you want something like the attached screenshot???

cynthia


use_spanrows_report.png
Contributor
Posts: 31

Re: Proc Report - Border color problem

No.  the screen shot is what it is currently producing.

I would like to have the first two columns have a black  border line on the last row. So, the last row of USA, I want a black border on the bottom of origin and make columns.   I hope that is clear!  if not, i can draw a picture with excel 

SAS Super FREQ
Posts: 8,743

Re: Proc Report - Border color problem

My screen shot and your screen shot are different. I thought you wanted a border line when MAKE changed. That's the issue I was dealing with -- in your COMPUTE block for MAKE (where the CALL DEFINE error occurred), it looked to me like you wanted the border top to be black. Did you look at the screen shot that I attached to my post, the one entitled "use_spanrows_report.png"??

cynthia

Just to be really clear about what I am seeing, the output on the left is what gets created if your COMPUTE block for MAKE is fixed. I wasn't going to focus on the line at the bottom until The program was error free. The output on the right is what I created using a slightly different technique. As you can see, the interior lines are the same and my table has a line at the bottom.


compare_two_screenshots.png
Contributor
Posts: 31

Re: Proc Report - Border color problem

I LOVE your screen shot on the right!   It appears that the bold border at each origin break is gone from your example on the right.  I assume that bold border would still work with your solution, thus having a thicker line at each break of origin?  (Sorry that I have not responded timely, I have been in meetings)


Contributor
Posts: 31

Re: Proc Report - Border color problem

And to fully answer your question, I do want the line on MAKE when the value changes.  And I would like a thick border when origin changes.  PLUS the end of each page or the end of the report should have a full border as well.  I think it looks better when there is a bottom border at the end of the report and end of each page when there are multiple pages. 

Solution
‎05-18-2012 12:52 AM
SAS Super FREQ
Posts: 8,743

Re: Proc Report - Border color problem


Hi,
  
I would recommend that instead of trying to "overcontrol" the border lines, explore what you can get automatically by using the SPANROWS option, which is designed to do what you want to do (remove the interior lines when a group item spans multiple rows). Then if you need to control border lines if a table breaks across pages, you can investigate other techniques.

 

The program below is what I used to illustrate the alternate approach using SPANROWS. Instead of fiddling with border top and border bottom, I think using the LINE statement in a COMPUTE AFTER block is probably the most reliable way to do something at the end of the group for ORIGIN. You can make the LINE foreground and background the same color...RTF won't let you go too small on fontsize or cellheight, but I made the color of the LINE output gray so you could see where the thicker division would occur.

BTW, the reason you were getting an error message on your CALL DEFINE for invalid argument was because the only unquoted values for the 1st argument  that are allowed are _COL_ and _ROW_ -- any item name must be a quoted value. If you did have ACROSS items (which you didn't), then you might need absolute column numbers, and if you did use absolute column numbers, then would have to be quoted in the CALL DEFINE statement ('_c2_','style','style=...'). But for your case, I think the simple _COL_ would have worked. But since SPANROWS wll do what you want, it might be worthwhile to investigate that first.

cynthia


OPTIONS ORIENTATION=LANDSCAPE NODATE NONUMBER center papersize=letter
TOPMARGIN=0.5 CM BOTTOMMARGIN=0.5 cm leftmargin=.5 cm  rightmargin=.75 cm ;
ODS LISTING CLOSE;

ods rtf file="c:\temp\carsSample_revised2.rtf" startpage=No 

Title1;

footnote j=l font='Arial Narrow' h=6.5pt

         "^S={protectspecialchars=off "

         "pretext='\brdrt\brdrs\brdrw1 '}This is a footnote with a line.";

** some of your options, like headline, headskip and spacing;
** are ignored for ODS. They are LISTING only options;
** Also, a fontsize of .1, without any unit does not make sense to me,;
** so I changed them to explicit fontsize of 6.5pt -- which is pretty small;
** And since you have all character variables, changing the summary line,;
** does not make sense either, but I left it in.;
** I think it's probably more reliable to put a divider line after each ORIGIN;
** by using the LINE statement in a COMPUTE AFTER block, than to fiddle;
** with the bordertop and border bottom controls.;

proc report data=sashelp.cars nowindows  split='*' spanrows
  style(report)=[ cellspacing=0 bordercolor=black fontsize=6.5pt
        width=9.75 in font_face='Arial Narrow' cellpadding=2]
  style(header)=[ color=Black fontsize=6.5pt font_face='Arial Narrow' ]
  style(column)=[ color=black fontsize=6.5pt font_face='Arial Narrow' ]
  style(summary)=[ color=cx3e3d73 backgroundcolor=cxaeadd9 fontsize=6.5pt textalign=r font_face='Arial Narrow'];
  where make in ('Scion', 'Land Rover', 'Isuzu','MINI', 'Porsche', 'Jeep', 'Hummer');
  column Origin Make  drivetrain type ;
   define Origin  /  group   'Origin';  
   define Make  /  group   'Make'   ;
   define drivetrain / group  "Drive Train" ;  
   define Type / group   'Type'   ;
   break after origin /;
   compute after origin / style={color=cxcccccc background=cxcccccc
                                 fontsize=.1pt cellheight=.1pt};
     line ' ';
   endcomp;
run;
ods rtf close;

Contributor
Posts: 31

Re: Proc Report - Border color problem

Brilliant solution! I encorporated your code into my proc report.  I really like the grey color, it is not as harsh as the thick black.  It looks terrific!  Love it!  My customer will be very excited as well.  

Thanks for letting me know why I was getting the call define error.  As you tell, I am new to proc report, I didn't see this mentioned anywhere in the documentation.  I did notice example using the ('_c2_','style','style=...') , but I wasn't for sure when and when not to use it.  I know the fontsize=.1pt is crazy.  It was my attempt to fit 36 columns on a page, which somehow, it working.  I use goptions when I have a combo barchart and proc report on the same page.  I had trouble with the goptions statements, so throwing bits and pieces of sample code I found on internet is the results you are seeing.  I should really understand what each option or goption is controlling a little better.  I'm learning!

I do have a gchart and proc report to fit on a page for this project.  So, space is critical!   I have 400 hundred pages of gcharts & proc report combinations to create, so i am ever so grateful for your quicky advice.  I am treading new territory with the ODS environment as well.  So, again, I am most appreciative of your careful advice.

OK, now I am ready to stop testing modifications because and on with creating the first report of many.

SAS Super FREQ
Posts: 8,743

Re: Proc Report - Border color problem

Hi:

  For more information about CALL DEFINE, the documentation is a good place to start:

http://support.sas.com/documentation/cdl/en/proc/63079/HTML/default/viewer.htm#n1b1be5822k8nnn1s1ucv...

  The complete list of possible values for the 1st argument, the column-id, are listed in the doc and repeated here for future readers:

"column-id

specifies a column name or a column number (that is, the position of the column from the left edge of the report). A column ID can be one of the following:

--a character literal (in quotation marks) that is the column name

--a character expression that resolves to the column name

--a numeric literal that is the column number

--a numeric expression that resolves to the column number

--a name of the form '_Cn_', where n is the column number

--the automatic variable _COL_, which identifies the column that contains the report item that the compute block is attached to"

  In spite of the order in which these possible values are listed, I think that the most used value for column-id is _COL_ -- but quotes would be needed as soon as you move outside _COL_ (or _ROW_), unless you were going to use numeric expressions or numeric literals for column id values.

cynthia

Contributor
Posts: 31

Re: Proc Report - Border color problem

THANK YOU!

Oh, by the way, I just added page numbers to my reports.  I searched in SAS communites, found one of your responses regarding page numbering.  And voila, I have page numbers!!!  I was stressing about how to insert, but it's SO easy!   Just put this into my footnote:

 

'^{pageof}'

So, you are helping me from problems reported back in 2009!  I think that is neat!  I am learning a great deal from you!  Thanks again!

SAS Super FREQ
Posts: 8,743

Re: Proc Report - Border color problem

Thanks! Good to know.

Searching for previous forum postings is just one place to start researching an issue. Actually, that would probably be the second place to search. The first place to search would be at support.sas.com, because if there is a known defect for an issue, there is usually a Tech Support note with a workaround, if any. Also, general usage questions also find their way into Tech Support notes, too.

For example, page numbers of the form Page X of Y have been popular for so long that there are several Tech Support notes on the topic, specifically related to RTF:

http://support.sas.com/kb/24/042.html

http://support.sas.com/kb/15/727.html

http://support.sas.com/kb/3/466.html

cynthia

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 1934 views
  • 5 likes
  • 2 in conversation