The SAS Output Delivery System and reporting techniques

Proc report and border for across variables

Reply
Contributor
Posts: 23

Proc report and border for across variables

Hi,

I'm wondering if there is some simple way to obtain a border with different width at every change of an across variable in a report.

The result should be really similar to this one http://support.sas.com/kb/50/229.html but I do not know how many values the across variable has, so it wuold be necessary to cout it and generate a call define line for every o them. Isn't there a better and more feasable solution?

 

Thanks Smiley Happy

SAS Super FREQ
Posts: 9,424

Re: Proc report and border for across variables

Hi:
  I'm not sure I understand what you want. The example you referenced shows a divider at the change of a ROW. The ACROSS variables would be a change in the COLUMN. The divider line spans the whole row, would you want your thicker border line to go down the whole column or be limited to the header area only?

What code have you tried? What does your data structure look like? With macro processing, you can count the number of ACROSS variables and use that count for a CALL DEFINE and COMPUTE blocks. Another question is how do you define "different width" for the border and what is your destination of interest? The example was for RTF, do you want RTF also?

Cynthia

Contributor
Posts: 23

Re: Proc report and border for across variables

Posted in reply to Cynthia_sas

Hi Cynthia... My bad, was looking at lot of pages and paste the wrong link as exaple. The right one is this:

http://support.sas.com/kb/39/652.html

My output destination is an excelxp, so the code is really similar to the rtf of the example. The thicker boarder should go on the entire column and not just on the header. The solution of coount the across values and generate the rest of the report is the same I was thinking about, bt was wondering if there is a more convenient way to do that, since I have lots of table to generate, with variable number of across values and multiple across on the same table. An exaple of the actual report is this, where the border should go between each of SPECIALITA_AMMISSIONE and ANNO columns

 

proc report data=SDO_APS missing
                    style(column)=[borderwidth=1px]
                    style(header)=[borderwidth=1px];
            where ANNO ge '2017' and ISTITUTO eq "&&IST_&ISTID.." and SPECIALITA_EFF_AMMISSIONE in (&LISTA_REPARTI. '' '00');
            options missing=" ";
            columns SPECIALITA_EFF_AMMISSIONE SPECIALITA_AMMISSIONE,ANNO,(NR_DIMISSIONI NR_GIORNATE_DEG);

            define SPECIALITA_EFF_AMMISSIONE / group format=$SPECIAL_TOT.;
            define SPECIALITA_AMMISSIONE / across format=$SPECIAL_TOT.;
            define ANNO / across '';
            define NR_DIMISSIONI / analysis 'Dimissioni' format=20. style(column)={tagattr='format:#,##0'};
            define NR_GIORNATE_DEG / mean 'Media GG deg.' format=20.1 style(column)={tagattr='format:#,##0.00'};

            rbreak after / summarize style=[background=yellow fontweight=bold];
            compute SPECIALITA_EFF_AMMISSIONE;
                if _BREAK_='_RBREAK_' then do;
                    SPECIALITA_EFF_AMMISSIONE="--";
                end;
            endcomp;
        run;
Super User
Posts: 13,913

Re: Proc report and border for across variables

Please describe the reason you want an unknown number of thicknesses for border lines.

When I try to image a set of these tables I can clearly imagine the thickness for the value "ABC" being different in two different tables for any number of reasons and that would cause a question of "Why the different thickness for the same value? What is the table designer trying to indicate with that?"

 

And if there are enough unknown values the variation is thickness could well be small to determine OR you would have some lines so thick as to possibly compromise legibility.

 

So, what is the actual purpose of these line thickness changes?

 

Here's example code that does this for sashelp.class.

data work.mkfmt;
  set sashelp.class;
  fmtname='LWT';
  type='C';
  start=strip(name);
  label=catt(_n_,'px');
  keep fmtname type start label;
run;

proc format library=work cntlin=work.mkfmt;
run;

proc report data=sashelp.class nowd;
   column sex name,height;
   define sex /group;
   define name / across style=[borderbottomwidth=$LWT.];
run;

Now tell my the underlined for William should be so much thicker than for Alfred.

 

Look at it in html (result viewer) before sending to Excel. I suspect Excel may do things such as rounding to a standard Excel line width value.

SAS Super FREQ
Posts: 9,424

Re: Proc report and border for across variables

Hi,

 

The bottom line is that there's nothing easier that will allow you to automatically alter the borderwidth, as you envision. The Tech Support note illustrates the best approach. The only thing that might make it easier would be to use Macro processing to get the number of ACROSS items and indicate where you want the thicker borders. But before you venture into the work of macro-izing it, I'd try to hard code it in a few examples.

I would be asking myself whether there's another way to call attention to the different across groups, that's easier to implement -- like a very light background color for the cells.

  I think that @ballardw is correct that Excel is not going to respect your varying border widths so you might want to test that out before you much further.
Cynthia

Super User
Posts: 13,913

Re: Proc report and border for across variables

Posted in reply to Cynthia_sas

The steps I took to create a format can be semi automated with a macro involving proc sql and a data step to build the control data set used by proc format but you still have the issue that enough "across" values can create a very thick line (if the ods destination will play nice). Note that my example of using PX for the width was to pick something smallish. You could use IN, CM or MM units as well but would likely need some multiplication of the _n_ value.

 

Alternating background colors might be another approach.

Contributor
Posts: 23

Re: Proc report and border for across variables

Thanks to both for the answers.

Well, yes, what I would like to obtain is something like 

image.png

 

 

 

 

Where for different tables the year and Ga and Gb groups would be different, depending on the data. I have cases where the by values are 20/30/40, and having a table with all plain columns and border does not help reading.

Another solution would be the one ballardw has proposed to varying the background of each maybe inner by group.

But as far as I understand, the only way is to correctly preoprocess the data, and then apply some macro to write all the needed call define.. Smiley Happy

SAS Super FREQ
Posts: 9,424

Re: Proc report and border for across variables

Hi:
 
  The different border widths on your picture are imperceptible to me. I don't see a difference in thickness. Take a look at this TABULATE example: http://support.sas.com/kb/25/addl/fusion25401_1_odstab10.htm -- http://support.sas.com/kb/25/401.html -- in particular, the 2nd one that is red/yellow in color. The colors are garish, but with a mostly black/white report and nice muted colors for the banding, it seems something like this would be easily readable.
 
  Using a modified version of the data and program in the Note, I produced this output:
tabulate_banding.png 


  Notice how the user-defined format for $SALECOL contains colors for all possible values (which you can do programmatically), but the colors in the format are only used when the data has the values for SALETYPE. Then, note the use of s=< parent > on the TABLE statement, which causes the banding down the columns.
 
  Here's the code that produced the output.
 



  data fakedata;
    infile datalines;
    input region $ citysize $ pop product $ saletype $
          quantity amount;
datalines;
   NC S   25000 A100 R 150   3750.00
   NE S   37000 A100 R 200   5000.00
   SO S   48000 A100 R 410  10250.00
   WE S   32000 A100 R 180   4500.00
   NC M  125000 A100 R 350   8750.00
   NE M  237000 A100 R 600  15000.00
   SO M  348000 A100 R 710  17750.00
   WE M  432000 A100 R 780  19500.00
   NE L  837000 A100 R 800  20000.00
   SO L  748000 A100 R 760  19000.00
   WE L  932000 A100 R 880  22000.00
   NC S   25000 A100 W 150   3000.00
   NE S   37000 A100 W 200   4000.00
   WE S   32000 A100 W 180   3600.00
   NC M  125000 A100 W 350   7000.00
   NE M  237000 A100 W 600  12000.00
   SO M  348000 A100 W 710  14200.00
   WE M  432000 A100 W 780  15600.00
   NC L  625000 A100 W 750  15000.00
   NE L  837000 A100 W 800  16000.00
   SO L  748000 A100 W 760  15200.00
   WE L  932000 A100 W 880  17600.00
   NC S   25000 A200 R 165   4125.00
   NE S   37000 A200 R 215   5375.00
   SO S   48000 A200 R 425  10425.00
   WE S   32000 A200 R 195   4875.00
   NC M  125000 A200 R 365   9125.00
   NE M  237000 A200 R 615  15375.00
   SO M  348000 A200 R 725  19125.00
   WE M  432000 A200 R 795  19875.00
   NE L  837000 A200 R 815  20375.00
   SO L  748000 A200 R 775  19375.00
   WE L  932000 A200 R 895  22375.00
   NC S   25000 A200 W 165   3300.00
   NE S   37000 A200 W 215   4300.00
   WE S   32000 A200 W 195   3900.00
   NC M  125000 A200 W 365   7300.00
   NE M  237000 A200 W 615  12300.00
   SO M  348000 A200 W 725  14500.00
   WE M  432000 A200 W 795  15900.00
   NC L  625000 A200 W 765  15300.00
   NE L  837000 A200 W 815  16300.00
   SO L  748000 A200 W 775  15500.00
   WE L  932000 A200 W 895  17900.00
   NC S   25000 A300 R 157   3925.00
   NE S   37000 A300 R 208   5200.00
   SO S   48000 A300 R 419  10475.00
   WE S   32000 A300 R 186   4650.00
   NC M  125000 A300 R 351   8725.00
   NE M  237000 A300 R 610  15250.00
   SO M  348000 A300 R 714  17850.00
   WE M  432000 A300 R 785  19625.00
   NE L  837000 A300 R 806  20150.00
   SO L  748000 A300 R 768  19200.00
   WE L  932000 A300 R 880  22000.00
   NC S   25000 A300 W 157   3140.00
   NE S   37000 A300 W 208   4160.00
   WE S   32000 A300 W 186   3720.00
   NC M  125000 A300 W 351   7020.00
   NE M  237000 A300 W 610  12200.00
   SO M  348000 A300 W 714  14280.00
   WE M  432000 A300 W 785  15700.00
   NC L  625000 A300 W 757  15140.00
   NE L  837000 A300 W 806  16120.00
   SO L  748000 A300 W 768  15360.00
   WE L  932000 A300 W 880  17600.00
      NC S   24356 A101 S 150   3432.00
   NE S   37000 A101 S 200   4356.00
   SO S   48000 A101 S 410   9423.00
   WE S   32000 A101 S 180   4500.00
   NC M  124356 A101 S 350   8750.00
   NE M  237000 A101 S 600  11432.00
   SO M  348000 A101 S 710  16492.00
   WE M  432000 A101 S 780  18743.00
   NE L  837000 A101 S 800  21873.00
   SO L  748000 A101 S 760  18754.00
   WE L  932000 A101 S 880  21874.00
   NC S   24356 A101 N 150   2873.00
   NE S   37000 A101 N 200   4000.00
   WE S   32000 A101 N 180   3600.00
   NC M  124356 A101 N 350   7000.00
   NE M  237000 A101 N 600  12000.00
   SO M  348000 A101 N 710  14200.00
   WE M  432000 A101 N 780  15600.00
   NC L  624356 A101 N 750  11432.00
   NE L  837000 A101 N 800  16000.00
   SO L  748000 A101 N 760  15200.00
   WE L  932000 A101 N 880  17600.00
   NC S   24356 A201 S 165   4125.00
   NE S   37000 A201 S 215   5375.00
   SO S   48000 A201 S 425  10425.00
   WE S   32000 A201 S 195   4875.00
   NC M  124356 A201 S 365   9125.00
   NE M  237000 A201 S 615  15375.00
   SO M  348000 A201 S 725  19125.00
   WE M  432000 A201 S 795  19875.00
   NE L  837000 A201 S 815  20375.00
   SO L  748000 A201 S 775  19375.00
   WE L  932000 A201 S 895  22375.00
   NC S   24356 A201 N 165   3300.00
   NE S   37000 A201 N 215   4300.00
   WE S   32000 A201 N 195   3900.00
   NC M  124356 A201 N 365   7300.00
   NE M  237000 A201 N 615  12300.00
   SO M  348000 A201 N 725  14500.00
   WE M  432000 A201 N 795  15900.00
   NC L  624356 A201 N 765  15300.00
   NE L  837000 A201 N 815  16300.00
   SO L  748000 A201 N 775  15500.00
   WE L  932000 A201 N 895  17900.00
   NC S   24356 A301 S 157   3925.00
   NE S   37000 A301 S 208   5200.00
   SO S   48000 A301 S 419  10475.00
   WE S   32000 A301 S 186   4650.00
   NC M  124356 A301 S 351   8725.00
   NE M  237000 A301 S 610  15250.00
   SO M  348000 A301 S 714  17850.00
   WE M  432000 A301 S 785  19625.00
   NE L  837000 A301 S 806  20150.00
   SO L  748000 A301 S 768  19200.00
   WE L  932000 A301 S 880  21874.00
   NC S   24356 A301 N 157   3140.00
   NE S   37000 A301 N 208   4160.00
   WE S   32000 A301 N 186   3720.00
   NC M  124356 A301 N 351   7020.00
   NE M  237000 A301 N 610  12200.00
   SO M  348000 A301 N 714  14280.00
   WE M  432000 A301 N 785  15700.00
   NC L  624356 A301 N 757  15140.00
   NE L  837000 A301 N 806  16120.00
   SO L  748000 A301 N 768  15360.00
   WE L  932000 A301 N 880  17600.00
   ;
  run;


  proc format;
    value $salefmt 'N'='Internet'
                   'R'='Retail'
				   'S'='Special'
                   'W'='Wholesale';

    value $salecol 'N'='verylightgreen'
                   'R'='verylightred'
				   'S'='verylightblue'
                   'W'='lightyellow';

  run;



 /* The user-defined format $SALECOL on the Classlev statement */
 /* determines the colors to be applied to the class level    */
 /* values for the variable SALETYPE.                         */
 /* Keyword variable Sum have parents Quantity and Amount and */
 /* these in turn have parent SALETYPE.                       */

  ods html path='c:\temp' body="odstab10.htm" style=pearl;
  ods listing close;


 proc tabulate data=fakedata s={fontweight=bold};
    where saletype in ('R', 'W');
    title '1) Uses Salecol for 2 values';
    class region citysize saletype;
    classlev saletype   / s={background=$salecol.};
    var quantity amount / s=< parent >;
    keyword sum         / s=< parent >;
    format saletype $salefmt.;
    label region="Region" citysize="Citysize";
    label quantity="Quantity" amount="Amount";
    keylabel all="Total" sum=' ';

 /* The parent is Saletype, which precedes Quantity and  */
 /* Amount in the dimension expression.                  */
 
    table region*citysize,
         saletype=' '*
         (quantity*f=COMMA6. amount*f=dollar10.)*
         {s=< parent >{foreground=black}}/ 
         misstext='Missing';
 run;

 
 proc tabulate data=fakedata s={fontweight=bold};
    where saletype in ('N', 'R', 'W');
    title '2) Uses Salecol for 3 values';
    class region citysize saletype;
    classlev saletype   / s={background=$salecol.};
    var quantity amount / s=< parent >;
    keyword sum         / s=< parent >;
    format saletype $salefmt.;
    label region="Region" citysize="Citysize";
    label quantity="Quantity" amount="Amount";
    keylabel all="Total" sum=' ';
    table region*citysize,
         saletype=' '*
         (quantity*f=COMMA6. amount*f=dollar10.)*
         {s=< parent >{foreground=black}}/ 
         misstext='Missing';
 run;


 proc tabulate data=fakedata s={fontweight=bold};
    title '3) Uses Salecol for all 4 values';
    class region citysize saletype;
    classlev saletype   / s={background=$salecol.};
    var quantity amount / s=< parent >;
    keyword sum         / s=< parent >;
    format saletype $salefmt.;
    label region="Region" citysize="Citysize";
    label quantity="Quantity" amount="Amount";
    keylabel all="Total" sum=' ';
    table region*citysize,
         saletype=' '*
         (quantity*f=COMMA6. amount*f=dollar10.)*
         {s=< parent >{foreground=black}}/ 
         misstext='Missing';
 run;

ods html close;



Cynthia

Contributor
Posts: 23

Re: Proc report and border for across variables

Posted in reply to Cynthia_sas

Hi, thanks for the sudgestion. I will try to apply this on my case.

In regards to my table example, there are just 2 border thikness, that is what I want; I'm not interesting in having different border size, just one bolder and one smaller depending on if the border divide cells inside a group or outside it. IE on the picture, row A, thin border between 1 and 2 (both in GA group), bolder border between 2 and 3 (going from Ga to Gb group), and so on!

 

Luca

Ask a Question
Discussion stats
  • 8 replies
  • 129 views
  • 0 likes
  • 3 in conversation