PROC REPORT - Displaying All Groups from Group Variable

[GBL__]

Hello all,

I am creating a reporting package for my team, and I am curious if I am able to include all potential groupings in a PROC REPORT output, even if no actual values are associated?

We classify a loans delinquency based on the number of days the loan is past due.  We calculate this by subtracting the current months end date by the the loans next due date to get a numerical number of days past due.  We then place those numbers into "buckets", like this:

PROC FORMAT LIBRARY=userfmt.formats ;
		VALUE validation_delinquency_days61_ (MULTILABEL)
				1  - 10	   =  "   1-10"
				11 - 30	   =  "  11-30" 
				31 - 60    =  "  31-60"
				61 - 90    =  "  61-90"
				91 - 120   =  " 91-120"
				121 - 150  =  "121-150"
				151 - 180  =  "151-180"
				181 - 210  =  "181-210"
				211 - high =  "211+   "
		other  =  "      0"
		;
RUN ;

I am curious if I can include all of the "buckets" above in the PROC REPORT output, even if no loans fall within one of the ranges?

Here is the output from one client, and you can see that there are no loans within their portfolio in the "121-150" and "181-210" ranges:

Can I include all ranges, with zeroes for their count, percentage, and total gross?

THank you very much for your help!

[PaigeMiller · Accepted solution]

Use the PRELOADFMT option, as in this example: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=proc&docsetTarget=p1kkf0f...

[GBL__]

Thank you for the reply!

Should I include the COMPLETEROWS option in the PROC REPORT Statement, as well?

Also, there was this error: WARNING: The format for variable VALIDATION_DELINQUENCY_c cannot be preloaded. A finite set of formatted values cannot be produced from the format. The format is not
recognized, is a SAS format, calls a function, or contains a nested format in its definition. Preload will have no effect.

In case it helps, here is the PROC REPORT code:

PROC REPORT DATA=t_delinquencysort MISSING COMPLETEROWS style(header)=[fontfamily="Albany AMT"] style(column)=[fontfamily="Albany AMT"] ;
SYSECHO "Printing Validation Delinquency" ;

COLUMNS VALIDATION_DELINQUENCY_c &delinquency_balance_field. &delinquency_balance_field.=DELQBAL,(PCTSUM SUM) ;

DEFINE VALIDATION_DELINQUENCY_c / GROUP "Validation Delinquency" MLF RIGHT ORDER=data PRELOADFMT ;
DEFINE &delinquency_balance_field. / N "Count" style(column)=[cellwidth=.7in] format=comma9. ;
DEFINE DELQBAL / "" format=dollar18.2 ;
DEFINE PCTSUM / "Percent of Field" style(column)=[cellwidth=1.2in] format=percent9.2 ;
DEFINE SUM / "&delinquency_balance_field." style(column)=[cellwidth=1.4in] format=dollar18.2 ;

RBREAK AFTER / SUMMARIZE style=header[font_style=italic] ;

COMPUTE AFTER / style=[fontsize=3 fontfamily="Calibri" font_style=italic font_weight=bold just=center] ;
	CALL DEFINE("VALIDATION_DELINQUENCY_c", "style", 'style=header[pretext="Grand Totals" textalign=right]') ;
	LINE " " ;
	LINE "%sysfunc(PROPCASE(%sysfunc(TRANSLATE(&client_name., ' ', '_')))) is currently using &line_filter." ;
	LINE " " ;
	LINE "^S={font_weight=medium} Delq Filter Used: None" ;
	LINE " " ;
ENDCOMP ;
RUN ;

[PaigeMiller]

Can you please show us the log, including the parts of the program where data set t_delinquency is created, and also the log for PROC REPORT (all the way down to the end of PROC REPORT and include the associated ERRORs NOTEs and WARNINGs)?

When providing the log, it is critical that you maintain the formatting of the log so we can see it exactly as SAS showed it to you, making it easier for us to use. To maintain the formatting of the log, click on the </> icon and paste the log as text into the window that appears. DO NOT SKIP THIS STEP.

[GBL__]

I figured it out. I did not explain as well as I should, the GROUP variable in the PROC REPORT output is previously defined with the format and a PUT statement when it is created.

I changed the PROC REPORT to use the days column and included the format in the report definition and it appears to work.

THank you again for your help! Much Appreciated!

[GBL__]

I just wanted to follow-up, in case someone else runs across this problem. 

But like I said, the field I was originally using was already created using a PUT Statement and the format:

SUM(CALCULATED DATE_OF_DATA, -next_due_date) AS CONT_DELQ_DAYS_c ,
PUT(CALCULATED CONT_DELQ_DAYS_c, contractual_delinquency_days61_.) AS CONTRACTUAL_DELINQUENCY_c ,
PUT(CALCULATED CONT_DELQ_DAYS_c, validation_delinquency_days61_.) AS VALIDATION_DELINQUENCY_c

So rather than use the field 'VALIDATION_DELINQUENCY_c' in the PROC REPORT layout, I needed to use the field 'CONT_DELQ_DAYS_c' and apply the format in the DEFINE Statement, along with the COMPLETEROWS Option in the PROC REPORT Statement and the PRELOADFMT Option in the DEFINE Statement:

PROC REPORT DATA=&client_number..&client_number._&medte._analysis MISSING COMPLETEROWS STYLE(header)=[fontfamily="Albany AMT"] STYLE(column)=[fontfamily="Albany AMT"] ;
SYSECHO "Printing Validation Delinquency" ;

	COLUMNS CONT_DELQ_DAYS_c &delinquency_balance_field. &delinquency_balance_field.=DELQBAL,(PCTSUM SUM) ;

	DEFINE CONT_DELQ_DAYS_c / GROUP "Validation Delinquency" MLF RIGHT format=validation_delinquency_days61_. PRELOADFMT ;
	DEFINE &delinquency_balance_field. / N "Count" STYLE(column)=[cellwidth=.7in] format=comma9. ;
	DEFINE DELQBAL / "" format=dollar18.2 ;
	DEFINE PCTSUM / "Percent of Field" STYLE(column)=[cellwidth=1.2in] format=percent9.2 ;
	DEFINE SUM / "&delinquency_balance_field." STYLE(column)=[cellwidth=1.4in] format=dollar18.2 ;

	RBREAK AFTER / SUMMARIZE STYLE=header[font_style=italic] ;

	COMPUTE AFTER / STYLE=[fontsize=3 fontfamily="Calibri" font_style=italic font_weight=bold just=center] ;
		CALL DEFINE("CONT_DELQ_DAYS_c", "style", 'style=header[pretext="Grand Totals" textalign=right]') ;
		LINE " " ;
		LINE "%sysfunc(PROPCASE(%sysfunc(TRANSLATE(&client_name., ' ', '_')))) is currently using &line_filter." ;
		LINE " " ;
		LINE "^S={font_weight=medium} Delq Filter Used: None" ;
		LINE " " ;
	ENDCOMP ;
RUN ;

Thank you again, @PaigeMiller , for your help!