BookmarkSubscribeRSS Feed
HN2001
Obsidian | Level 7

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 REPLY 1

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 947 views
  • 0 likes
  • 2 in conversation