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
... View more