proc report pdf help!

[new_sas_user_4]

Hello Everyone,

I am preparing my first ever pdf file 🙂 

Below are a few questions I have if you could please help out!!

I am grouping based on metric..(I could group based on metric AND dimension1 if that helps)  

Dimension1 has values 

Total

Total_split1

Total_split2

Total_SplitA

Total_SplitB

Total_SplitC

Total_SplitD

1.) I want a line before Total_SplitA but only for the rows under the "count" and "Region" columns..Is there a way to achieve this?

2.) How can I bold the Total row values?

3.) For every group in the metric I want a line which I obtain using

(compute dimension1;

if dimension1 = 'Total' then call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1]');

endcomp;

)

Is there a better way to do this?

Below is my code:

ods pdf file="&report_dir/PDF_file_draft1.pdf"  notoc;

proc report data=FILE1 headline headskip

style(report)=[cellspacing=0 cellpadding=0.5 frame = boxrules=groups font=(Arial, 7pt, bold) WIDTH=100%]

style(header)=[foreground=white background=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/group order=data;

define dimension1 /display ;

define Col_A/display  format = comma12.;

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 call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1]');

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;

ods pdf close;

Thank you 🙂

Below is the my expected format:

Total = Total_Split1 and Total_Split2

Total=Total_SplitA+Total_SplitB+Total_SplitC+Total_SplitD (split in a different way)

[Cynthia_sas]

Hi: Without data, nobody can test your code. In order to test your code, people have to make fake data and that means that have to guess the structure of your data. Are you sure your program is working? I don't see the dollar format being used for Col_Y and Col_Z.

You asked:
1.) I want a line before Total_SplitA but only for the rows under the "count" and "Region" columns..Is there a way to achieve this?

>> I don't understand this -- Are you saying that you do NOT want a line above Metric and Dimension1 in the desired output? This is not clear to me.

2.) How can I bold the Total row values?
>>You are already doing a test for dimension1='Total' in a compute block. Have you tried the following
compute dimension1;
  if dimension1 = 'Total' then
  call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1 font_weight=bold]');
endcomp;

3.) For every group in the metric I want a line which I obtain using compute dimension1;

Is there a better way to do this?

>> I don't know what you mean when you say "for every group in the metric".  Do you mean you want lines UNDER every single value for Dimension1? Or you want a line where you show it in the desired output? Since I can't run your code to see what you're getting now and since you only show your desired output, I can't compare what you're getting now to what you want to see what changes need to be made.

>> You could have turned off all the interior table lines using the JOURNAL style instead of the technique you used, but even so, you might try the PEARL or SAPPHIRE style to see if the LINES you get are more what you want.

Also, options like HEADLINE and HEADSKIP have no impact on ODS PDF, so they will be ignored. And, for many reasons, posted in previous forum entries, the use of the simple font name Arial is frequently ignored by ODS PDF. Typically for PDF, you need to specify "Arial Unicode MS" which is the preferred way for Adobe Acrobat to refer to the Arial font. Here's one posting: https://communities.sas.com/t5/General-SAS-Programming/Changing-font-size-of-superscript-characters/... and here's another: https://communities.sas.com/t5/ODS-and-Base-Reporting/TEMPLATE-STYLE-change-font-family-and-size/td-....

Cynthia

[new_sas_user_4]

Thanks Cynthia !

Below is the sample data and code:

data raw_data;
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
;
%let report_dir = %sysfunc(pathname(work));
ods pdf file="&report_dir/PDF_file_draft1.pdf" notoc;

proc report data=raw_Data headline headskip

style(report)=[cellspacing=0 cellpadding=0.5 frame = box rules=groups font=(Arial, 7pt, bold) WIDTH=100%]

style(header)=[foreground=white background=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/group order=data;

define dimension1 /display ;

define Col_A/display format = comma12.;

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 call define(_row_,'style','style=[bordertopcolor=black bordertopwidth=1]');

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;
ods pdf close;

This produces the below output:

I want the output to look like below :

- Horizontal line after Total_split2 (not under metric)

- Vertical line after the column metric

Also, if the first column (Ontario, Alberta) to be centered(like merge and center in excel) 🙂

Thank you !!

[Cynthia_sas · Accepted solution]

Hi:

  Thanks for providing the data. I took out HEADLINE and HEADSKIP since they did not serve any purpose as options and simplified your code and came up with this:

  The only thing that is different, to my eye, is that your divider between Total_splitA and Total_split2 does NOT go all the way over to the border for Metric/Dimension. When altering borders, the borders span the WHOLE cell, you can't start the border away from the left edge as you showed.

  You should have been getting notes in the log about the GROUP usage for Metric not being possible because the usage of Dimension1 was DISPLAY -- to fix that you just need to change the usage of Metric to ORDER.

  I simplified your code and highlighted the changes as shown below. I was explicit about the orientation and margin options with an OPTIONS statement.

Hope this helps,

Cynthia

[new_sas_user_4]

Thanks a lot Cynthia 🙂 This is perfect!!