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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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