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
This should help
PROC REPORT — different formats for different rows? - SAS Support Communities
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.