Border after every group

new_sas_user_4 — Fri, 27 Sep 2019 15:24:09 GMT

Hi Everyone,

Using the code below I am able to get the border after every region in the first page.

I would like to have border after every region on page 2 as well.

How can I have this?

data raw_data1;
length metric $7 dimension1 $12 ;
input metric $ dimension1 $ Col_A Col_B Col_C Col_D Col_E Col_F Col_X Col_Y Col_Z ;
datalines;
Ontario Total 100 0.67 0.70 400 100 250 50 500 0.21
Alberta Total 70 0.65 0.56 200 60 150 40 400 0.23
Ontario Total_split1 40 0.51 0.75 250 70 140 40 300 0.22
Alberta Total_split1 30 0.45 0.72 120 30 120 10 130 0.26
Ontario Total_split2 60 0.37 0.75 150 30 110 10 200 0.23
Alberta Total_split2 40 0.29 0.77 80 30 30 30 270 0.04
Ontario Total_splitA 20 0.21 0.80 100 30 90 10 120 0.25
Alberta Total_splitA 10 0.13 0.82 90 10 50 10 100 0.04
Ontario Total_splitB 20 0.05 0.85 150 40 100 10 130 0.26
Alberta Total_splitB 20 0.45 0.87 80 20 60 12 70 0.86
Ontario Total_splitC 50 0.85 0.90 30 10 10 20 100 0.35
Alberta Total_splitC 30 1.25 0.92 10 12 15 5 80 2.82
Ontario Total_splitD 10 1.65 0.95 -50 -10 50 10 150 0.56
Alberta Total_splitD 10 2.05 0.97 20 18 25 13 150 1.12
;

data raw_data2;
length metric $7 dimension1 $12 ;
input metric $ dimension1 $ Col_A Col_B Col_C Col_D Col_E Col_F Col_X Col_Y Col_Z ;
datalines;
Ontario Total_splitD 40 0.51 0.75 250 70 140 40 300 0.22
Ontario SPlit_E 30 0.45 0.72 120 30 120 10 130 0.26
Ontario Split_F 60 0.37 0.75 150 30 110 10 200 0.23
Ontario Split_X 60 0.37 0.75 150 30 110 10 200 0.23
Alberta Total_splitD 40 0.29 0.77 80 30 30 30 270 0.04
Alberta SPlit_E 20 0.21 0.80 100 30 90 10 120 0.25
Alberta SPlit_F 10 0.13 0.82 90 10 50 10 100 0.04
Alberta Split_X 10 0.13 0.82 90 10 50 10 100 0.04
Sask Total_splitD 20 0.05 0.85 150 40 100 10 130 0.26
Sask split_E 20 0.45 0.87 80 20 60 12 70 0.86
Sask split_F 50 0.85 0.90 30 10 10 20 100 0.35
Sask Split_X 50 0.85 0.90 30 10 10 20 100 0.35
Manitoba Total_splitD 30 1.25 0.92 10 12 15 5 80 2.82
Manitoba split_E 10 1.65 0.95 -50 -10 50 10 150 0.56
Manitoba split_F 10 2.05 0.97 20 18 25 13 150 1.12
Manitoba Split_X 10 2.05 0.97 20 18 25 13 150 1.12
;

%macro pg(table_name);
proc report data=&table_name spanrows
style(report)=[cellspacing=0 cellpadding=0.5 frame = box rules=groups bordercolor=black font=(Arial, 7pt, bold)]
style(header)=[foreground=black
font=(Arial, 7pt, bold) borderbottomwidth=0 bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=black NOBREAKSPACE=ON ]
style(column)=[font=(Arial, 7pt) foreground=black background=CXFFFFFF JUST = C VJUST=C bordertopwidth=0 borderrightwidth=0 borderleftwidth=0 bordercolor=white ];
Columns
metric dimension1
("Count" Col_A Col_B Col_C Col_D Col_E Col_F)
("Region" Col_X Col_Y Col_Z);

define metric/order order=data style(column)={vjust=m cellwidth=1in};
define dimension1 /display
style(header)=[bordercolor=gray borderleftwidth=0.5]
style(column)=[bordercolor=gray borderleftwidth=0.5 cellwidth=1in]
;

define Col_A/display format = comma12. style(column)={just=r cellwidth=0.02in};
define Col_B/ display format = percentn12.1;
define Col_C/display format = percentn12.1 ;
define Col_D/format = comma12. ;
define Col_E/display format = comma12.;
define Col_F/display format = comma12.;
define Col_X/display format = comma12.;
define Col_Y/display format = dollar12.2;
define Col_Z/display format = dollar12.;

compute dimension1;
if dimension1='Total' then do;call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1 background=#D9D9D9]');end;
if dimension1='Split_X' then do;call define(_row_,'style','style=[borderbottomcolor=gray borderbottomwidth=1]');
end;
endcomp;

compute Col_A; call define(_COL_,'style','style=[borderleftcolor=black borderleftwidth=1]');endcomp;
compute Col_X; call define(_COL_,'style','style=[borderleftcolor=black borderleftwidth=1]');endcomp;
run;
%mend pg;

%let report_dir = %sysfunc(pathname(work));
ods pdf file="&report_dir/PDF_file_draft1.pdf" notoc;
Title j=c font='Arial' h=18pt "Data Summary";%pg(raw_data1);
Title j=c font='Arial' h=18pt "Data Detail";%pg(raw_data2);
ods pdf close;

Thank you!!

Re: Border after every group

ballardw — Fri, 27 Sep 2019 15:37:28 GMT

You might want to attach an example of the generated PDF and explain exactly what is needed in relation to values of lines and such.

Since you are using a COMPUTE block that tests for EXACT values then you would need to include all of the possible values in all of your data sets

compute dimension1;
if dimension1='Total' then do;call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1 background=#D9D9D9]');end;
if dimension1='Split_X' then do;call define(_row_,'style','style=[borderbottomcolor=gray borderbottomwidth=1]');
end;
endcomp;

Or perhaps use a COMPUTE AFTER dimension1.

Please describe your problem in terms of you variables. You say that you want "border after every region" but there is no variable named Region in you example data. So I have to guess that you mean dimension1.

Re: Border after every group

new_sas_user_4 — Fri, 27 Sep 2019 15:46:31 GMT

Currently I see the below outputs for pg2 and pg1

I meant after every metric(which has region values)..below is how I WANT page 2 to look.

I want the border after every group of metric

topic Re: Border after every group in ODS and Base Reporting

Border after every group

Re: Border after every group

Re: Border after every group