topic Re: Varying format by row in Proc Report in SAS Programming

Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 19:18:35 GMT

Based on this example

https://communities.sas.com/t5/ODS-and-Base-Reporting/PROC-REPORT-different-formats-for-different-rows/td-p/403157

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%

Re: Varying format by row in Proc Report

Tom — Tue, 10 Jun 2025 20:07:53 GMT

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?

Re: Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 21:10:32 GMT

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.

Re: Varying format by row in Proc Report

PaigeMiller — Tue, 10 Jun 2025 21:22:35 GMT

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.

Re: Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 21:45:04 GMT

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

Re: Varying format by row in Proc Report

Tom — Tue, 10 Jun 2025 21:50:28 GMT

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
;;;;

Re: Varying format by row in Proc Report

Tom — Tue, 10 Jun 2025 22:01:07 GMT

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

Re: Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 22:37:23 GMT

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
;;;;

Re: Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 22:40:28 GMT

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

Re: Varying format by row in Proc Report

PaigeMiller — Tue, 10 Jun 2025 22:45:12 GMT

@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.

Re: Varying format by row in Proc Report

Tom — Tue, 10 Jun 2025 23:02:17 GMT

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

Re: Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 23:03:41 GMT

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

Re: Varying format by row in Proc Report

Tom — Tue, 10 Jun 2025 23:06:33 GMT

@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.

Re: Varying format by row in Proc Report

Batman — Tue, 10 Jun 2025 23:15:29 GMT

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.

Re: Varying format by row in Proc Report

Tom — Tue, 10 Jun 2025 23:28:22 GMT

@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.