I'm trying to generate report that look like
test --> TAT REPORT 24Dec2019 | ||||
TAT | less than 5 hours | 5 - 8 hours | More than 8 hours | Grand Total |
FTR | 0 | 0 | 0 | 0 |
% | 100% | 0% | 0% | |
FTNR | 1 | 1 | 108 | 110 |
% | 1% | 1% | 98% | |
Grand Total | 17 | 1 | 108 | 126 |
% | 13% | 1% | 86% |
Below is the code i'm using
PROC REPORT DATA=test_b
style(header)= {FOREGROUND = WHITE BACKGROUND = MAROON FONT = ('Calibri',7.5PT) FONTWEIGHT = BOLD BORDERCOLOR =BLACK}
style(column)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center}
style(report)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center} ;
column ("test --> TAT REPORT &REPORT_DT" tat 'less than 5 hours'n '5-8 hours'n 'More than 8 hours'n 'Grand Total'n);
RUN;
Output:
test --> TAT REPORT 27DEC2019 | ||||
TAT | less than 5 hours | 5-8 hours | More than 8 hours | Grand Total |
FTNR | 1 | 1 | 107 | 109 |
% | 0.0091743 | 0.0091743 | 0.9816514 | . |
FTR | 16 | . | 1 | 17 |
% | 0.9411765 | . | 0.0588235 | . |
I'm unable to figure out is how to calculate the "Grand Total" and "Total percentage".
Hi @prajakta
Here is a quite more complex code to achieve the desired output using a proc report.
Please note that the input data is as follows -> to be adapted in case it is not exactly yours (maybe run a proc freq before)
data test_b;
length TAT $ 100;
input TAT $ 1-4 Duration 6 count 8-10;
datalines;
FTR 1 16
FTR 2 0
FTR 3 0
FTNR 1 1
FTNR 2 1
FTNR 3 108
;
run;
proc format;
value Duration
1 = "Less than 5 hours"
2 = "5 - 8 hours"
3 = "More than 8 hours";
run;
proc report data=test_b
style(header)={FOREGROUND=WHITE BACKGROUND=MAROON
FONT=('Calibri', 7.5PT) FONTWEIGHT=BOLD BORDERCOLOR=BLACK}
style(column)={FONT=('Calibri', 7.5PT) BORDERCOLOR=BLACK just=center}
style(report)={FONT=('Calibri', 7.5PT) BORDERCOLOR=BLACK just=center};
column ("test --> TAT REPORT &REPORT_DT" TAT Duration, (count pct _dummy) ntot);
define TAT / group;
define Duration / across order=data format=Duration.;
define count / analysis noprint;
define pct / computed f=percent8.0 '%' noprint;
define _dummy / computed '';
define ntot / computed 'Grand Total';
rbreak after /summarize;
break after TAT / summarize ;
compute before TAT;
den=_C2_ + _C5_ + _C8_;
endcomp;
compute after TAT;
TAT = "%";
_c3_ = _c2_ / den;
_c6_ = _c5_ / den;
_c9_ = _c8_ / den;
endcomp;
compute _dummy / char;
if _BREAK_ = "_RBREAK_" or (_C1_ in ("FTR" "FTNR") and _BREAK_ ="") then do;
_C4_ = _C2_;
_C7_ = _C5_;
_C10_= _C8_;
end;
else do;
_C4_ = put(_c2_ / den, percent8.0);
_C7_ = put(_c5_ / den, percent8.0);
_C10_= put(_c8_ / den, percent8.0);
end;
endcomp;
compute ntot;
if _BREAK_ = "_RBREAK_" or (_C1_ in ("FTR" "FTNR") and _BREAK_ ="") then ntot=_C2_ + _C5_ + _C8_;
endcomp;
compute after;
TAT = "Grand Total";
_c3_ = _c2_ / 126;
_c6_ = _c5_ / 126;
_c9_ = _c8_ / 126;
endcomp;
run;
Output;
You might be better off using PROC FREQ to compute the percents and counts, and this will produce a table very very close to what you show. If that table isn't exactly what you want, you could manipulate the output of PROC FREQ in a DATA step to be exactly what you want, and then use PROC REPORT.
Maybe there's a way to get these rows of percents using only PROC REPORT, but I'm sure the above is easier.
What a shame to use PROC REPORT or PROC TABULATE here when PROC FREQ gives the exact same table with much less work.
Hi @prajakta
Could you please provide some sample data?
Hi @prajakta
Here is a quite more complex code to achieve the desired output using a proc report.
Please note that the input data is as follows -> to be adapted in case it is not exactly yours (maybe run a proc freq before)
data test_b;
length TAT $ 100;
input TAT $ 1-4 Duration 6 count 8-10;
datalines;
FTR 1 16
FTR 2 0
FTR 3 0
FTNR 1 1
FTNR 2 1
FTNR 3 108
;
run;
proc format;
value Duration
1 = "Less than 5 hours"
2 = "5 - 8 hours"
3 = "More than 8 hours";
run;
proc report data=test_b
style(header)={FOREGROUND=WHITE BACKGROUND=MAROON
FONT=('Calibri', 7.5PT) FONTWEIGHT=BOLD BORDERCOLOR=BLACK}
style(column)={FONT=('Calibri', 7.5PT) BORDERCOLOR=BLACK just=center}
style(report)={FONT=('Calibri', 7.5PT) BORDERCOLOR=BLACK just=center};
column ("test --> TAT REPORT &REPORT_DT" TAT Duration, (count pct _dummy) ntot);
define TAT / group;
define Duration / across order=data format=Duration.;
define count / analysis noprint;
define pct / computed f=percent8.0 '%' noprint;
define _dummy / computed '';
define ntot / computed 'Grand Total';
rbreak after /summarize;
break after TAT / summarize ;
compute before TAT;
den=_C2_ + _C5_ + _C8_;
endcomp;
compute after TAT;
TAT = "%";
_c3_ = _c2_ / den;
_c6_ = _c5_ / den;
_c9_ = _c8_ / den;
endcomp;
compute _dummy / char;
if _BREAK_ = "_RBREAK_" or (_C1_ in ("FTR" "FTNR") and _BREAK_ ="") then do;
_C4_ = _C2_;
_C7_ = _C5_;
_C10_= _C8_;
end;
else do;
_C4_ = put(_c2_ / den, percent8.0);
_C7_ = put(_c5_ / den, percent8.0);
_C10_= put(_c8_ / den, percent8.0);
end;
endcomp;
compute ntot;
if _BREAK_ = "_RBREAK_" or (_C1_ in ("FTR" "FTNR") and _BREAK_ ="") then ntot=_C2_ + _C5_ + _C8_;
endcomp;
compute after;
TAT = "Grand Total";
_c3_ = _c2_ / 126;
_c6_ = _c5_ / 126;
_c9_ = _c8_ / 126;
endcomp;
run;
Output;
@prajakta wrote:
I'm trying to generate report that look like
test --> TAT REPORT 24Dec2019 TAT less than 5 hours 5 - 8 hours More than 8 hours Grand Total FTR 0 0 0 0 % 100% 0% 0% FTNR 1 1 108 110 % 1% 1% 98% Grand Total 17 1 108 126 % 13% 1% 86%
Below is the code i'm using
PROC REPORT DATA=test_b
style(header)= {FOREGROUND = WHITE BACKGROUND = MAROON FONT = ('Calibri',7.5PT) FONTWEIGHT = BOLD BORDERCOLOR =BLACK}
style(column)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center}
style(report)= {FONT = ('Calibri',7.5PT) BORDERCOLOR =BLACK just= center} ;
column ("test --> TAT REPORT &REPORT_DT" tat 'less than 5 hours'n '5-8 hours'n 'More than 8 hours'n 'Grand Total'n);
RUN;
Output:
test --> TAT REPORT 27DEC2019 TAT less than 5 hours 5-8 hours More than 8 hours Grand Total FTNR 1 1 107 109 % 0.0091743 0.0091743 0.9816514 . FTR 16 . 1 17 % 0.9411765 . 0.0588235 .
I'm unable to figure out is how to calculate the "Grand Total" and "Total percentage".
You will find working with SAS that often creating variables such as you "less than 5" and such is not flexible and can make certain types of calculations harder than necessary. You can create groups of values with a FORMAT to display desired text for the actual underlying values. And then using the format with the simpler data of one record per value generating the report relatively easy.
The example below creates a custom format to create 3 groups from a range of values. Then an example data set that has two values for a TAT variable and randomly creates a number Hour values. Then uses Proc Tabulate (Proc Report does really like to display statistics the way you have shown your desired value) to group the hour values, count and determine the row percentage for each group, a grand total column to show the counts and percent (which is going to be 100) as well as summary row to show the hour group totals.
proc format library=work; value demo 1 -<5 = 'less than 5 hours' 5 - 8 = '5-8 hours' 8<-high='More than 8 hours' ; data example; tat = 'FTNR'; Do i= 1 to 47; hour = rand('integer',1,15); output; end; tat = 'FTR'; Do i= 1 to 59; hour = rand('integer',1,17); output; end; run; proc tabulate data=example; class tat'
class hour/ order=internal; format hour demo.; table (tat All='Total')*(n rowpctn='%'*f=8.4), hour=' ' All='Grand Total' ; run;
Tabulate will allow the style overrides but is done differently than in Proc report depending on what you want. CLASSLEV statement sets style elements for the row/ column header cells of CLASS variables for example.
One of the very strong advantages of this approach is if someone asks to show 4 groups at different intervals, or 2 groups. Then the only changes needed are to create an additional format and use that format in the Procedure code. Your approach would require going back through the data and creating additional variables.
The groups created by formats in this way are honored by almost all of the analysis or graphing procedures. So again code that works with one format only needs to have the format changed to create different analysis or graphs. Sort behavior is also easier to manage with numeric values underlying the format.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.