topic Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows in ODS and Base Reporting

PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

GBL__ — Wed, 18 Dec 2019 19:56:30 GMT

Hello all,

I am trying to add an additional summary row, before my "Grand Total" summary row, that sums the values of specific rows.

Here is the code I am using and the output:

PROC REPORT DATA=&client_number..&client_number._&medte._pcart style(header)=[fontfamily='Albany AMT'] style(column)=[fontfamily='Albany AMT'] ;

SYSECHO "PROC REPORT - Printing Contractual Delinquency" ;

	COLUMNS CONTRACTUAL_DELINQUENCY GROSS_BALANCE GROSS_BALANCE=GROSSBAL,(PCTSUM SUM) ;

	DEFINE CONTRACTUAL_DELINQUENCY / GROUP 'Contractual Delinquency' format=$delinq_61_. RIGHT ;
	DEFINE GROSS_BALANCE / N 'Count' style(column)=[cellwidth=.7in] format=comma9. ;
	DEFINE GROSSBAL / ' ' format=dollar18.2 ;
	DEFINE PCTSUM / 'Percent of Field' style(column)=[cellwidth=1.1in] format=percent9.2 ;
	DEFINE SUM / 'Gross Balance' style(column)=[cellwidth=1.3in] format=dollar18.2 ;

	RBREAK AFTER / SUMMARIZE style=[font_style=italic font_weight=medium] ;

	COMPUTE AFTER / style=[fontsize=3 fontfamily='Calibri' font_style=italic font_weight=bold just=c] ;
		CALL DEFINE("CONTRACTUAL_DELINQUENCY", "style", "style=[pretext='Grand Totals' textalign=right]") ;
		LINE " " ;
		LINE "Filter: No Filter Used" ;
		LINE " " ;
	ENDCOMP ;

RUN ;

What I am hoping to accomplish is to add a row or line above "Grand Totals" that summarizes the values from 91-120, 121-150, 181-210, and 211+ and says "90+ Totals" above Grand Totals.

Please let me know if I am being unclear, or if this is actually not possible. I appreciate any feedback. Thank you!

Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

ballardw — Wed, 18 Dec 2019 22:07:24 GMT

Your code references a format $delinq_61_.

If you share the code for that format we can show you how to create another custom format that creates the groups you want. And then use that format instead of format=$delinq_61_.

If you want that 90+ IN Addition to the current rows you are showing the format would be a MULTILABEL format, which are practically impossible to test without actual data.

So provide data step code showing an example of your data by following Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

GBL__ — Wed, 18 Dec 2019 22:13:23 GMT

Hi ballardw,

The format is actually already using multilabel, but as far as I know multilabel formats are not recognized within proc report, only means, summary, and tabulate.

PROC FORMAT ;
		VALUE $delinq_61_ (MULTILABEL)
				  "0"  =  "      0"
				 "30"  =  "  31-60"
				 "60"  =  "  61-90"
				 "90"  =  " 91-120"
				"120"  =  "121-150"
				"150"  =  "151-180"
				"180"  =  "181-210"
				"210"  =  "211+   "
"90","120","150","180","210"  =  "Total 90+ Delinquency"
		;
RUN ;

My hope was to be able to sum the specific rows within proc report, rather than use tabulate

Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

ballardw — Wed, 18 Dec 2019 23:05:32 GMT

Example from the Proc Report documentation using a multilabel format:

proc format;
  value agelfmt (multilabel notsorted)
     11='11'
     12='12'
     13='13'
     14='14'
     15='15'
     16='16'
     11-12='11 or 12'
     13-14='13 or 14'
     15-16='15 or 16'
     low-13='13 and below'
     14-high='14 and above' ;
run;

title "GROUP Variable with MLF Option";

proc report data=sashelp.class;

   column age ('Mean' height weight);

   define age / group mlf format=agelfmt. 'Age Group' order=data preloadfmt; 
   define height / mean format=6.2 'Height (in.)';
   define weight / mean format=6.2 'Weight (lbs.)';
run; title;

Note the MLF option on the define for age to indicate the format is multilabel . You may need to add the NOTSORTED to the format definition to get the order as desired and/or play with the order of the value range statements in proc format. Sorting with character values that look numeric can get pretty flaky.

And if you used code to add a character variable created from numeric values to create your variable you might be better off using the numeric value (order is much more consistent).

Your multilabel formats will also often interact with ORDER= options in the proc, so you may want to experiment with sorting and Order =.

All that is why I mentioned multilabel formats without data are hard to test.

I have a demonstration program with Proc Tabulate, which I use more than Report, with multiple interactions between ORDER, PRELOADFMT and NOTSORTED (or default) with different orders of the group definitions in the Value ranges.

Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

Cynthia_sas — Wed, 18 Dec 2019 23:17:20 GMT

Hi:
This paper shows several ways to add extra lines to PROC REPORT: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf .

Hope this helps,
Cynthia

Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

GBL__ — Thu, 19 Dec 2019 14:25:22 GMT

I do not know where I read that MLF was NOT an option for PROC REPORT, but it may have been implemented somewhat recently?

Either way, thank you for pointing out that I can use it, appreciate your help!

Re: PROC REPORT - Adding Additional Summarize Row - Summing Specific Rows

GBL__ — Thu, 19 Dec 2019 14:26:13 GMT

Thank you, Cynthia! This does indeed help, great paper!