PROC REPORT - IF/THEN to count number of specific variable(s) value(s) -- SAS EG 7.15

[GBL__]

Hello all,

I am trying to create a high level detail report that contains the number of loans, max loan date, max last payment date, total gross and net balances, as well as the number of: empty/blank next due dates, $0 payment amounts, $0 amount financed, and 0% apr all broken down by branch.

I feel like my code is really close to what I am wanting, but the values for the last three columnsare not working correctly.

*********************************************************************************************************************
** Branch Level Statistics ;

TITLE1 "  " ;
TITLE2 "&clientname - Branch Level Statistics" ;
TITLE3 "  " ;

PROC REPORT DATA=atl0.&clientname._&medte._PCART(KEEP=BRANCH_NAME GROSS_BALANCE NET_BALANCE LOAN_DATE DATE_OF_LAST_PAYMENT NEXT_DUE_DATE NET_BALANCE APR AMOUNT_FINANCED 
	MONTHLY_PAYMENT_AMOUNT MONTHLY_TERM TOTAL_OF_PAYMENTS DOWN_PAYMENT CONTRACTUAL_DELINQUENCY) ;

	COLUMNS BRANCH_NAME GROSS_BALANCE=COUNT LOAN_DATE=MAX_LOAN_DATE DATE_OF_LAST_PAYMENT=MAX_LAST_PAID_DATE GROSS_BALANCE=TOTAL_GROSSBAL NET_BALANCE EMPTY_NEXT_DUE ZERO_PAYMENT_AMOUNT ZERO_AMOUNT_FINANCED ZERO_APR ;

	DEFINE BRANCH_NAME / GROUP 'Branch Name' WIDTH=30 CENTER ;
	DEFINE COUNT / N 'Count' FORMAT=COMMA6. WIDTH=6 CENTER ;
	DEFINE MAX_LOAN_DATE / MAX 'Max Loan Date' FORMAT=MMDDYY10. CENTER ;
	DEFINE MAX_LAST_PAID_DATE / MAX 'Max Last Paid Date' FORMAT=MMDDYY10. CENTER ;
	DEFINE TOTAL_GROSSBAL / SUM 'Total Gross Balance' FORMAT=DOLLAR18.2 CENTER ;
	DEFINE NET_BALANCE / SUM 'Total Net Balance' FORMAT=DOLLAR18.2 CENTER ;
	DEFINE EMPTY_NEXT_DUE / COMPUTED 'Empty Next Due Date' CENTER ;
	DEFINE ZERO_PAYMENT_AMOUNT / COMPUTED 'Zero Payment Amount' CENTER ;
	DEFINE ZERO_AMOUNT_FINANCED / COMPUTED 'Zero Amount Financed' CENTER ;
	DEFINE ZERO_APR / COMPUTED 'Zero APR' CENTER ;

	COMPUTE EMPTY_NEXT_DUE ;

		IF NEXT_DUE_DATE=. THEN
			EMPTY_NEXT_DUE=N(NEXT_DUE_DATE) ;
	ENDCOMP ;

	COMPUTE ZERO_PAYMENT_AMOUNT ;

		IF MONTHLY_PAYMENT_AMOUNT=0.00 THEN
			ZERO_PAYMENT_AMOUNT=N(MONTHLY_PAYMENT_AMOUNT) ;
	ENDCOMP ;

	COMPUTE ZERO_AMOUNT_FINANCED ;

		IF AMOUNT_FINANCED=0.00 THEN
			ZERO_AMOUNT_FINANCED=N(AMOUNT_FINANCED) ;
	ENDCOMP ;

	COMPUTE ZERO_APR ;

		IF APR=0 THEN
			ZERO_APR=N(APR) ;
	ENDCOMP ;
RUN ;
TITLE1 ;
TITLE2 ;
TITLE3 ;

Basically, I am looking for the number of loans (count) where MONTHLY_PAYMENT_AMOUNT = 0.00, AMOUNT_FINANCED = 0.00, and APR = 0

When I run the code above, all other fields populate correctly but those last three are displayed as missing.  This is what the current ouput looks like, which is perfect except for the last three columns:

Is there a way to do what I am trying to do?  Do I need to use another procedure?

Appreciate your feedback.  Thank you very much.

[ballardw]

Proc report will not let you use any values for variables that you do not include in either column statement (for variables from your data set) or that do not appear in columns to the left of your calculation.

So you need to add MONTHLY_PAYMENT_AMOUNT, AMOUNT_FINANCED and APR variables as columns left of the computed columns. You might also need to do this for NEXT_DUE_DATE. 

Add the NOPRINT option in DEFINE statements for them so they do not actually appear in your table output.

[GBL__]

Hi ballardw,

First off, thank you for the reply.  I appreciate it.

I am a little confused, however.

I added NEXT_DUE_DATE, MONTHLY_PAYMENT_AMOUNT, AMOUNT_FINANCED, and APR to the columns statement and defined each as NOPRINT:

PROC REPORT DATA=atl0.&client_name._&medte._PCART(KEEP=BRANCH_NAME GROSS_BALANCE NET_BALANCE LOAN_DATE DATE_OF_LAST_PAYMENT NEXT_DUE_DATE NET_BALANCE APR AMOUNT_FINANCED 
	MONTHLY_PAYMENT_AMOUNT MONTHLY_TERM TOTAL_OF_PAYMENTS DOWN_PAYMENT CONTRACTUAL_DELINQUENCY);

	COLUMNS BRANCH_NAME GROSS_BALANCE=COUNT LOAN_DATE=MAX_LOAN_DATE DATE_OF_LAST_PAYMENT=MAX_LAST_PAID_DATE GROSS_BALANCE=TOTAL_GROSSBAL NET_BALANCE NEXT_DUE_DATE MONTHLY_PAYMENT_AMOUNT AMOUNT_FINANCED APR EMPTY_NEXT_DUE ZERO_PAYMENT_AMOUNT ZERO_AMOUNT_FINANCED ZERO_APR;

	DEFINE BRANCH_NAME / GROUP 'Branch Name' WIDTH=30 CENTER;
	DEFINE COUNT / N 'Count' FORMAT=COMMA6. WIDTH=6 CENTER;
	DEFINE MAX_LOAN_DATE / MAX 'Max Loan Date' FORMAT=MMDDYY10. CENTER;
	DEFINE MAX_LAST_PAID_DATE / MAX 'Max Last Paid Date' FORMAT=MMDDYY10. CENTER;
	DEFINE TOTAL_GROSSBAL / SUM 'Total Gross Balance' FORMAT=DOLLAR18.2 CENTER;
	DEFINE NET_BALANCE / SUM 'Total Net Balance' FORMAT=DOLLAR18.2 CENTER;
	DEFINE NEXT_DUE_DATE / ANALYSIS NOPRINT;
	DEFINE MONTHLY_PAYMENT_AMOUNT / ANALYSIS NOPRINT;
	DEFINE AMOUNT_FINANCED / ANALYSIS NOPRINT;
	DEFINE APR / ANALYSIS NOPRINT;
	DEFINE EMPTY_NEXT_DUE / COMPUTED 'Empty Next Due Date' CENTER;
	DEFINE ZERO_PAYMENT_AMOUNT / COMPUTED 'Zero Payment Amount' CENTER;
	DEFINE ZERO_AMOUNT_FINANCED / COMPUTED 'Zero Amount Financed' CENTER;
	DEFINE ZERO_APR / COMPUTED 'Zero APR' CENTER;

	COMPUTE EMPTY_NEXT_DUE;

		IF NEXT_DUE_DATE=. THEN
			EMPTY_NEXT_DUE=N(NEXT_DUE_DATE);
	ENDCOMP;

	COMPUTE ZERO_PAYMENT_AMOUNT;

		IF MONTHLY_PAYMENT_AMOUNT=0.00 THEN
			ZERO_PAYMENT_AMOUNT=N(MONTHLY_PAYMENT_AMOUNT);
	ENDCOMP;

	COMPUTE ZERO_AMOUNT_FINANCED;

		IF AMOUNT_FINANCED=0.00 THEN
			ZERO_AMOUNT_FINANCED=N(AMOUNT_FINANCED);
	ENDCOMP;

	COMPUTE ZERO_APR;

		IF APR=0 THEN
			ZERO_APR=N(APR);
	ENDCOMP;
RUN;
TITLE1;
TITLE2;
TITLE3;

When I run the above code i get four notes:

NOTE: Variable NEXT_DUE_DATE is uninitialized.

NOTE: Variable MONTHLY_PAYMENT_AMOUNT is uninitialized.

NOTE: Variable AMOUNT_FINANCED is uninitialized.

NOTE: Variable APR is uninitialized.

I did not receive these notes previously.  Do I need to change/alter the definitions in the COMPUTE blocks?  I removed  ANALYSIS from thos four variables DEFINE lines and the same notes appeared.

Thank you again for your feedback!

[ballardw]

Please try to avoid lines of code that are more than about 80 characters. It is very hard to read code when we have to scroll left and right and the scroll can't appear in the same part of the page as the text such as your KEEP statement and the columns statement that run off the window to the right.

You may have to provide a little example data  and what you expect the result to be.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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.

I am not sure why you are attempting this:

	COMPUTE EMPTY_NEXT_DUE;

		IF NEXT_DUE_DATE=. THEN
			EMPTY_NEXT_DUE=N(NEXT_DUE_DATE);
	ENDCOMP;

As the N statistic won't count missing values and I would expect

DEFINE Next_due_date/ N 'Empty Next Due Date' CENTER;

to get non-missing count.

[GBL__]

Thanks again for the feedback, it is appreciated.

Here is some sample data:

data client_data;
    infile datalines dsd truncover;
    input Branch_Name: $14. Loan_Date: mmddyy10. Date_of_Last_Payment: mmddyy10.
 Gross_Balance :5.2 Net_Balance :5.2 Next_Due_Date :MMDDYY10. 
Monthly_Payment_Amount :4.2 Amount_Financed :5.2 APR :3.3;
datalines;
Bulk_Purchase, 05/30/2019, , 1255.20, 1201.30, 07/15/2019, 129.56, 1300.00, 14.999
Bulk_Purchase, 05/02/2019, 05/31/2019, 3355.20, 3329.30, , 187.36, 0.00, 0.00
Ohio, 03/15/2019, 05/31/2019, 389.69, 322.44, 07/01/2019, 289.99, 7805.45, 5.896
Virginia, 03/01/2019, 05/31/2019, 189.45, 100.00, 07/01/2019, 89.66, 8901.32, 9.999
Ohio, 01/01/2019, 05/15/2019, 4569.65, 4510.00, 06/16/2019, 78.69, 8999.99, 36.668
Bulk_Purchase, 02/02/2019, 04/28/2019, 6654.56, 3329.30, 05/31/2019, 187.36, 5600.00, 0.00
;;;;

Here is what I am hoping for:

Branch Name Count Max Loan Date Max Last Paid Date Total Gross Balance Total Net Balance Empty Next Due Date Zero Payment Amount Zero Amount Financed Zero APR

Bulk_Purchase	3	05/30/2019	05/31/2019	$11,264.96	$7,859.90	1	0	1	2
Ohio	2	03/15/2019	05/31/2019	$4,959.34	$4,832.44	0	0	0	0
Virginia	1	03/01/2019	05/31/2019	$189.45	$100.00	0	0	0	0

I am trying to get counts of the number of loans per Branch that have zero % APR, zero amount financed, a zero dollar monthly payment, and/or a blank/empty next due.

here is an updated code, per your previous suggestion (and others):

PROC REPORT DATA=client_data MISSING;

	COLUMNS NEXT_DUE_DATE MONTHLY_PAYMENT_AMOUNT AMOUNT_FINANCED 
APR BRANCH_NAME GROSS_BALANCE=COUNT LOAN_DATE=MAX_LOAN_DATE 
DATE_OF_LAST_PAYMENT=MAX_LAST_PAID_DATE GROSS_BALANCE=TOTAL_GROSSBAL 
NET_BALANCE EMPTY_NEXT_DUE ZERO_PAYMENT_AMOUNT ZERO_AMOUNT_FINANCED ZERO_APR dummy;

	DEFINE NEXT_DUE_DATE / N NOPRINT;
	DEFINE MONTHLY_PAYMENT_AMOUNT / N NOPRINT;
	DEFINE AMOUNT_FINANCED / N NOPRINT;
	DEFINE APR / N NOPRINT;
	DEFINE BRANCH_NAME / GROUP 'Branch Name' WIDTH=30 CENTER;
	DEFINE COUNT / N 'Count' FORMAT=COMMA6. WIDTH=6 CENTER;
	DEFINE MAX_LOAN_DATE / MAX 'Max Loan Date' FORMAT=MMDDYY10. CENTER;
	DEFINE MAX_LAST_PAID_DATE / MAX 'Max Last Paid Date' FORMAT=MMDDYY10. CENTER;
	DEFINE TOTAL_GROSSBAL / SUM 'Total Gross Balance' FORMAT=DOLLAR18.2 CENTER;
	DEFINE NET_BALANCE / SUM 'Total Net Balance' FORMAT=DOLLAR18.2 CENTER;
	DEFINE EMPTY_NEXT_DUE / COMPUTED 'Empty Next Due Date' CENTER;
	DEFINE ZERO_PAYMENT_AMOUNT / COMPUTED 'Zero Payment Amount' CENTER;
	DEFINE ZERO_AMOUNT_FINANCED / COMPUTED 'Zero Amount Financed' CENTER;
	DEFINE ZERO_APR / COMPUTED 'Zero APR' CENTER;
	DEFINE dummy / NOPRINT;

	COMPUTE EMPTY_NEXT_DUE;
		IF NEXT_DUE_DATE=. THEN;
			EMPTY_NEXT_DUE=N(NEXT_DUE_DATE);
	ENDCOMP;

	COMPUTE ZERO_PAYMENT_AMOUNT;
		IF MONTHLY_PAYMENT_AMOUNT=0.00 THEN;
			ZERO_PAYMENT_AMOUNT=N(MONTHLY_PAYMENT_AMOUNT);
	ENDCOMP;

	COMPUTE ZERO_AMOUNT_FINANCED;
		IF AMOUNT_FINANCED=0.00 THEN;
			ZERO_AMOUNT_FINANCED=N(AMOUNT_FINANCED);
	ENDCOMP;

	COMPUTE ZERO_APR;
		ZERO_APR=0;
		IF APR=0.00 THEN DO;
			ZERO_APR+1;
	ENDCOMP;
RUN;

I think the previous PROC REPORT code is working well enough, except for the last three columns.  I am not sure if if it is possible to do what I am trying to do with PROC REPORT?

Thanks again for any and all feedback  (including criticism for terrible code and ideas)!

[ballardw · Accepted solution]

I don't do a lot of conditional calculations in Proc report, often just because I forget syntax and these types of (screwy) notes.

I would likely have used a data step to add indicator variables for the MONTHLY_PAYMENT_AMOUNT=0.00  type things and then summed them.

data want;

    set client_data;

    zpflag = (MONTHLY_PAYMENT_AMOUNT=0.00 );

    zfflag  = (AMOUNT_FINANCED=0.00 );

    zapr    = (Apr = 0);

run;

and then use these three variables as the column and the default sum will count the number of original values with 0.

[GBL__]

Thanks again, ballardw, for your responses and feedback regarding my problem.

Your suggestion to create flag variables and then use the default sum worked perfectly.

here is my completed code and output using your advice:

data client_report;
    set &client_data(KEEP=BRANCH_NAME GROSS_BALANCE NET_BALANCE LOAN_DATE DATE_OF_LAST_PAYMENT NEXT_DUE_DATE NET_BALANCE APR AMOUNT_FINANCED 
	MONTHLY_PAYMENT_AMOUNT MONTHLY_TERM TOTAL_OF_PAYMENTS DOWN_PAYMENT CONTRACTUAL_DELINQUENCY);
	_nflag  = (NEXT_DUE_DATE=.);
    _pflag  = (MONTHLY_PAYMENT_AMOUNT=0.00 );
    _fflag  = (AMOUNT_FINANCED=0.00 );
    _aflag  = (APR = 0);
run;

proc report data=tebo_report;

	columns BRANCH_NAME GROSS_BALANCE=COUNT LOAN_DATE=MAX_LOAN_DATE DATE_OF_LAST_PAYMENT=MAX_LAST_PAID_DATE GROSS_BALANCE=TOTAL_GROSSBAL NET_BALANCE _nflag=EMPTY_NEXT_DUE _pflag=ZERO_PAYMENT_AMOUNT _fflag=ZERO_AMOUNT_FINANCED _aflag=ZERO_APR;

	define BRANCH_NAME / group 'Branch Name' width=30 center;
	define COUNT / n 'Count' format=comma6. width=6 center;
	define MAX_LOAN_DATE / max 'Max Loan Date' format=mmddyy10. center;
	define MAX_LAST_PAID_DATE / max 'Max Last Paid Date' format=mmddyy10. center;
	define TOTAL_GROSSBAL / sum 'Total Gross Balance' format=dollar18.2 center;
	define NET_BALANCE / sum 'Total Net Balance' format=dollar18.2 center;
	define EMPTY_NEXT_DUE / sum 'Empty Next Due Date' center;
	define ZERO_PAYMENT_AMOUNT / sum 'Zero Payment Amount' center;
	define ZERO_AMOUNT_FINANCED / sum 'Zero Amount Financed' center;
	define ZERO_APR / sum 'Zero APR' center;

	rbreak after / summarize;
	compute after;
		MAX_LOAN_DATE=.;
		MAX_LAST_PAID_DATE=.;
		EMPTY_NEXT_DUE=.;
		ZERO_PAYMENT_AMOUNT=.;
		ZERO_AMOUNT_FINANCED=.;
		ZERO_APR=.;
	endcomp;

run;

And the output:

Thanks again, it is very much appreciated!