Formatting proc report in different rows and columns

[HN2001]

Hi,

I am a novice when it comes to formatting and proc report, so please forgive if this is an easy question to solve.

I have the following as an example of what I'm trying to do, the datalines I created for this post

data have;
length varnm $100. grade1 grade2 grade3 all 8.;
input varnm grade1 grade2 grade3 all 8.;
datalines;
Number 240 100 150 490
Mean 21.6 . 25.0 23.8
Std 3.5 . 3.9 3.6
Percent 49.47 . 63.31 56.17
;
run;

proc format;
value tab1na (default=11) .='N/A';
run;

ods excel file="C:\Users\xxxxx\Desktop\test.xlsx" OPTIONS(FLOW='Tables');
ods excel options(sheet_name="Table1");

proc report data=have;
column (varnm grade1 grade2 grade3 all);
define varnm / 'Stat' display;
define grade1 /'Grade 1' display;
define grade2 /'Grade 2' display;
define grade3 /'Grade 3' display;
define all /'all' display;
compute all;
if varnm in ("Percent") then do;
do i = 2 to 5;
call define(i,'format','7.2');
end;
end;
if varnm in ("Mean" "Std") then do;
do i = 2 to 5;
call define(i,'format','7.1');
end;
end;
endcomp;
run;

ods excel options(sheet_name="Table1a");

proc report data=have;
column (varnm grade1 grade2 grade3 all);
define varnm / 'Stat' display;
define grade1 /'Grade 1' display;
define grade2 /'Grade 2' display;
define grade3 /'Grade 3' display;
define all /'all' display;
compute all;
if varnm in ("Percent") then do;
do i = 2 to 5;
call define(i,'format','7.2');
end;
end;
if varnm in ("Percent") then do;
do i = 2 to 5;
call define(i,'format','tab1na.');
end;
end;
if varnm in ("Mean" "Std") then do;
do i = 2 to 5;
call define(i,'format','7.1');
end;
end;
if varnm in ("Mean" "Std") then do;
do i = 2 to 5;
call define(i,'format','tab1na.');
end;
end;
endcomp;
run;
ods excel close;

For Table1 I get:

Stat	Grade 1	Grade 2	Grade 3	all
Number	240	100	150	490
Mean	21.6	.	25.0	23.8
Std	3.5	.	3.9	3.6
Percent	49.47	.	63.31	56.17

For Table 1a I get:

Stat	Grade 1	Grade 2	Grade 3	all
Number	240	100	150	490
Mean	21.6	N/A	25	23.8
Std	3.5	N/A	3.9	3.6
Percent	49.47	N/A	63.31	56.17

The second outputted table is what I want, however, it is not formatting the mean row like I wanted.

I want the output to look like this

Stat	Grade 1	Grade 2	Grade 3	all
Number	240	100	150	490
Mean	21.6	N/A	25.0	23.8
Std	3.5	N/A	3.9	3.6
Percent	49.47	N/A	63.31	56.17

I think the issue is because when I run the second call define for formatting to N/A, it is actually overwriting the first format.

Is there a way to combine formatting for a row where if it is . it is formatted as N/A, if not . then format with 7.1 or 7.2 depending on the row?

Thank you

[PaigeMiller]

This should help

PROC REPORT — different formats for different rows? - SAS Support Communities