Varying format by row in Proc Report

[Batman]

Based on this example

https://communities.sas.com/t5/ODS-and-Base-Reporting/PROC-REPORT-different-formats-for-different-ro...

I wrote the following code.   See the last 3 compute blocks.

proc format;
   picture custpct low-high='0009.9%' (mult=10);  /*  Adjust format as needed  */
run;
proc report data=Tbl1;
	col GrpVar Lab Total MA_PDs PDPs;
	define GrpVar / Group noprint;
	define Lab / '' Group order=data;
	define Total / format=comma12.;
	define MA_PDs / format=comma12.;
	define PDPs / format=comma12.;
	compute before GrpVar / style = {just=left font_weight=bold};;
		length brkline $100;
		if grpVar=1 then brkline='Plans';
		else if grpVar=2 then brkline='Plans by tier count';
		else if GrpVar=3 then brkline='Formularies';
		else if GrpVar=4 then brkline='Beneficiaries';
		line brkline $100.; 
	endcomp;
	compute Total;
		if Lab='Percent' then call define('_c3_','format','custpct.');
	endcomp;
	compute MA_PDs;
		if Lab='Percent' then call define('_c4_','format','custpct.');
	endcomp;
	compute PDPs;
		if Lab='Percent' then call define('_c5_','format','custpct.');
	endcomp;
run;

It ran.  However, for some reason, the percentages are truncated.   The second "Percent" row as some double counting, but can I get the first and third to sum to 100.0%?

Table 1: Number of Plans and Formularies for PDPs and MA-PDs in 2025

Total MA-PDs PDPs Plans Number 5,244 4,770 474 Percent 100.0% 90.9% 9.0% Plans by tier count None 2 2 0 one 260 260 0 two 28 28 0 three 1 1 0 four 39 39 0 five 3,941 3,467 474 six 957 957 0 seven 16 16 0 Formularies Number 384 346 40 Percent 100.0% 90.1% 10.4% Beneficiaries Number 46,481,848 28,266,979 18,214,869 Percent 100.0% 60.8% 39.1%

[Tom]

I do not see any counting in this code at all.  Perhaps the the fact that the row percentages add up to 200% was created in the step that made the source dataset?

[Batman]

Sorry, the image of the output is out of whack.    Hopefully this is a better image.   The Report Procedure doesn't sum anything, I'm just using to display the exact contents of a dataset because it has so many more options than Proc Print.

Basically, the percentages in row Excel rows 11 and 26 should sum to 100.0%, but they don't with the code I used.

[PaigeMiller]

It is virtually impossible to fix your code without having (a representative portion of) the data in data st TBL1. Please provide the data as working SAS data step code (examples and instructions) and not in any other form. Excel files are not acceptable. Copy and paste from Excel are not acceptable. Screen captures of data are not acceptable.

[Batman]

How do I generate what you are asking for? Print it in the log?

[Tom]

Easier to PUT it to the log. Then copy the generated text and use it to make a data step to read it back in.

Here is a macro that will generate such code from a source dataset.

https://github.com/sasutils/macros/blob/master/ds2post.sas

For example if you run this macro call

%ds2post(sashelp.class)

It will write these lines to the SAS log.

data work.class (label='Student Data');
  infile datalines dsd dlm='|' truncover;
  input Name :$8. Sex :$1. Age Height Weight ;
datalines4;
Alfred|M|14|69|112.5
Alice|F|13|56.5|84
Barbara|F|13|65.3|98
Carol|F|14|62.8|102.5
Henry|M|14|63.5|102.5
James|M|12|57.3|83
Jane|F|12|59.8|84.5
Janet|F|15|62.5|112.5
Jeffrey|M|13|62.5|84
John|M|12|59|99.5
Joyce|F|11|51.3|50.5
Judy|F|14|64.3|90
Louise|F|12|56.3|77
Mary|F|15|66.5|112
Philip|M|16|72|150
Robert|M|12|64.8|128
Ronald|M|15|67|133
Thomas|M|11|57.5|85
William|M|15|66.5|112
;;;;

[Batman]

data work.tbl1 ;
  infile datalines dsd dlm='|' truncover;
  input GrpVar Lab :$100. Total MA_PDs PDPs ;
  format Total comma12.1 MA_PDs comma12.1 PDPs comma12.1 ;
  label MA_PDs='MA-PDs' ;
datalines4;
1|Number|5244|4770|474
1|Percent|100|90.9610983981693|9.03890160183066
2|None|2|2|0
2|one|260|260|0
2|two|28|28|0
2|three|1|1|0
2|four|39|39|0
2|five|3941|3467|474
2|six|957|957|0
2|seven|16|16|0
3|Number|384|346|40
3|Percent|100|90.1041666666666|10.4166666666666
4|Number|46481848|28266979|18214869
4|Percent|100|60.8129414303837|39.1870585696162
;;;;

[PaigeMiller]

---

@Batman wrote:

data work.tbl1 ;
  infile datalines dsd dlm='|' truncover;
  input GrpVar Lab :$100. Total MA_PDs PDPs ;
  format Total comma12.1 MA_PDs comma12.1 PDPs comma12.1 ;
  label MA_PDs='MA-PDs' ;
datalines4;
1|Number|5244|4770|474
1|Percent|100|90.9610983981693|9.03890160183066
2|None|2|2|0
2|one|260|260|0
2|two|28|28|0
2|three|1|1|0
2|four|39|39|0
2|five|3941|3467|474
2|six|957|957|0
2|seven|16|16|0
3|Number|384|346|40
3|Percent|100|90.1041666666666|10.4166666666666
4|Number|46481848|28266979|18214869
4|Percent|100|60.8129414303837|39.1870585696162
;;;;

---

We're not asking for the output as SAS data step code. We're asking for the data in data set TBL1, which you haven't yet shown us. I have given you a link to the instructions on how to do this.

[Tom]

Those values add up to 200% when you use PROC PRINT.  Your PROC REPORT code does not change the values.

[Batman]

The "Total" column is the sum of columns "MA_PDs" and "PDPs"

[Tom]

---

@Batman wrote:

The "Total" column is the sum of columns "MA_PDs" and "PDPs"

---

Can you start over and explain what your issue actually is?

Now that you have shared the data perhaps you can explain using example values to show more clearly what you want that is different from what you are getting.

[Batman]

Yes,  I'm trying to get the percentage rows to round rather than truncate.

This row, for example

1|Percent|100|90.9610983981693|9.03890160183066

 Gives me "100.0, 90.9, and 9.0" for example in the Report Procedure as I currently have it written (see my first post).  

I want it to be "100.0, 91.0 and 9.0"

I suspect the format procedure is the problem, but was hoping for some guidance on how to make it work.

[Tom]

---

@Batman wrote:

Yes,  I'm trying to get the percentage rows to round rather than truncate.

This row, for example

1|Percent|100|90.9610983981693|9.03890160183066

 Gives me "100.0, 90.9, and 9.0" for example in the Report Procedure as I currently have it written (see my first post).  

I want it to be "100.0, 91.0 and 9.0"

I suspect the format procedure is the problem, but was hoping for some guidance on how to make it work.

---

Well that is simple and we didn't need to see the complex PROC REPORT code to fix that. Use the ROUND option in the PICTURE statement.

proc format;
   picture custpct (round) low-high='0009.9%' (mult=10);  /*  Adjust format as needed  */
run;

554  data _null_;
555    set  tbl1 ;
556    where Lab = 'Percent';
557    put GrpVar= Lab=
558      / (Total MA_PDs PDPs) (=best12.)
559      / (Total MA_PDs PDPs) (=custpct.)
560      /
561    ;
562  run;

GrpVar=1 Lab=Percent
Total=100 MA_PDs=90.961098398 PDPs=9.0389016018
Total=100.0% MA_PDs=91.0% PDPs=9.0%

GrpVar=3 Lab=Percent
Total=100 MA_PDs=90.104166667 PDPs=10.416666667
Total=100.0% MA_PDs=90.1% PDPs=10.4%

GrpVar=4 Lab=Percent
Total=100 MA_PDs=60.81294143 PDPs=39.18705857
Total=100.0% MA_PDs=60.8% PDPs=39.2%

But rounding is not the issue for GRPVAR=3.  To figure out why the two values add up to 100.5% you will need to look into how the input dataset was generated.

[Tom]

And how different are the numbers when you just use PROC PRINT?

[Batman]

The percentage values look good in Proc Print, I just can't format the table the way I would like in Print.