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 🙂
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
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;
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.
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
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.
Thanks to both for the answers.
Well, yes, what I would like to obtain is something like
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.. 🙂
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:
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.