topic Re: Summary format report ideas in SAS Programming

Summary format report ideas

rajd1 — Wed, 10 Jun 2020 20:05:27 GMT

Hi,

I had asked a question about indenting using proc tabulate in my last post and i am using the same data here. I am trying to get some formatted templates that you might have used for exporting results to excel. All ideas are welcome. Thanks!

data testing;
input Name $ County $ MaxRun MinRun RUNS FIRSTD LASTD TIME Reduce $ Increase $;
datalines;
A X 5 2 4 130 135 122 No Yes
A X 5 2 4 870 885 122 No Yes
B Y 7 1 7 660 610 220 Yes No
B Y 7 1 7 660 610 220 Yes No
B Z 7 1 7 660 610 220 Yes No
C Z 3 1 5 485 430 220 Yes No
C X 4 1 4 195 190 220 Yes No
C X 4 1 4 195 190 220 Yes No
D Y 5 1 5 575 485 106 Yes No
D Y 5 1 5 800 795 106 Yes No
D Z 6 3 3 103 106 210 No Yes
A Z 6 1 6 590 585 210 Yes No
A X 9 1 9 835 870 210 No Yes
B X 9 1 9 835 870 210 No Yes
C Y 2 2 1 165 165 620 No No
C Y 1 3 9 112 535 234 Yes No
C Z 1 3 9 112 535 234 Yes No
;

/*Summary for quantitative variables*/
ODS TAGSETS.EXCELXP
file="path\check info.xml"
STYLE=statistical
OPTIONS (embedded_titles='yes' embedded_footnotes='yes' FitToPage = 'yes'
sheet_name="Quantitative" Orientation = 'landscape');
title "Quant Table";
proc tabulate data= Testing format=comma11.2;
CLASS county;
var MaxRun MinRun RUNS FIRSTD LASTD TIME;
table County * (MaxRun='Maximum Runs' MinRun='Minimum Runs' RUNS='RUNS' FIRSTD='1st' LASTD='Lst' TIME='TT'),(N mean*f=9.1 median*f=9.1 std*f=9.2)/rts=25 INDENT=4;
keylabel ALL = 'Total'
mean = 'Mean'
median = 'Median'
std = 'SD';
run;
/*Summary for categorical variables*/
STYLE=statistical
OPTIONS (embedded_titles='yes' embedded_footnotes='yes' FitToPage = 'yes'
sheet_name="Categorical" Orientation = 'landscape');
title "Categorical Table";
proc tabulate data= testing NOSEPS format=comma11.2;
class Reduce Increase/ missing;
table (Reduce='' Increase=''), (all pctn)/rts=25 INDENT=4;
keylabel
pctn = '%';
run;
ods tagsets.ExcelXP close;
ods html close;
run;

Re: Summary format report ideas

Reeza — Wed, 10 Jun 2020 20:21:54 GMT

Are you referring to style templates or templates for PROC REPORT? Or something else?

Is ODS EXCEL not an option either?

Re: Summary format report ideas

rajd1 — Wed, 10 Jun 2020 20:24:51 GMT

Yes ODS EXCEL is definitely another option. Yes to similar templates in PROC REPORT as well.
Similar templates in PROC tabulate will also be helpful.

Re: Summary format report ideas

JeffMeyers — Wed, 10 Jun 2020 20:56:45 GMT

I'm not sure if this would be helpful for you or not, but I have a macro dedicated to summarizing different types of data and outputting to different locations (like ODS EXCEL). There is an article here: https://communities.sas.com/t5/SAS-Communities-Library/Demographic-Table-and-Subgroup-Summary-Macro-TABLEN/ta-p/634030

I ran the following on your example data set and got the attached EXCEL file.


%tablen(data=testing, by=county, showpval=0,
    var=maxrun minrun runs firstd lastd time reduce increase,type=1 1 1 1 1 1 2 2,
    shading=2,outdoc=~/ibm/test.xlsx,destination=excel);

Re: Summary format report ideas

rajd1 — Wed, 10 Jun 2020 21:01:54 GMT

I literally downloaded your Macro an hour ago and was going to explore it! This is absolutely great because formatting such tables takes time and automation is the way to go!
On this post, i was looking more like custom formats. Thanks again!

Re: Summary format report ideas

Reeza — Wed, 10 Jun 2020 21:49:34 GMT

Creating Complex Reports paper by Cynthia Zender is old but a good reference to review first if you're just starting in SAS. Of course, you should explore the different default styles as well.

Art's book on Proc Report is also a good resource. For specifics that you need search on here for examples ie 'proc report traffic lighting' will give you examples on conditionally applying banded colours and such.