Formatting proc report in different rows and columns

HN2001 — Tue, 21 Mar 2023 17:24:34 GMT

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

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?

topic Formatting proc report in different rows and columns in SAS Programming

Formatting proc report in different rows and columns

Re: Formatting proc report in different rows and columns