topic Re: how to count the number of observations that meet certain conditions using proc tabulate in Statistical Procedures

how to count the number of observations that meet certain conditions using proc tabulate

UshaLatha — Wed, 10 Jan 2018 13:47:55 GMT

Hi All,

I need some help in analysis of residential mortgage data.

I have some fields like FICO score , LTV , Default flag in the data.

FICO	LTV	DEFAULT_FLAG
551	50	1
752	75	1
713	83	1
400	90	0

I need a matrix which can give the joint distribution of FICO and LTV for the default rate. For eg, I have 1000 loans, of which 239 have defaulted (i.e default_flag=1) , which have LTV<70 and FICO<710, so the default rate should be 239/1000=0.239.. Similarly distribution for all the bucket ranges of LTV and FICO score is needed.

My desired distribution table should be like this:

		LTV
		Low (<70)	Medium (70,80)	High(80,85)	Very high(>85)
FICO	Low (<710)	0.239	4.95	5.53	9.97
	Medium(710,750)	1.5	3.42	4.2	5.2
	High(750,755)	0.23	3.21	2.31	3.2
	Very high >755	0.34	0.26	0.78	1.34

The code I have used is as follows:

proc tabulate data=test;
format LTV ltv_fmt. FICO fico_fmt. ;
class FICO LTV;
var DEFAULT_FLAG;
table FICO, LTV*DEFAULT_FLAG=" "*(N="count" PCTN="%");
run;

This code is giving the count of rows (irrespective of value of default_flag value) which fall into that particular ranges of LTV and FICO. But I need count of rows where DEFAULT_FLAG=1.

Please help me with this.

Thanks

Re: how to count the number of observations that meet certain conditions using proc tabulate

HB — Wed, 10 Jan 2018 14:25:26 GMT

Can you subset the data before the Proc Tabulate?

data want;
	set have;
	if default_flag = 1;
run;

proc tabulate data=want

Re: how to count the number of observations that meet certain conditions using proc tabulate

UshaLatha — Wed, 10 Jan 2018 14:34:42 GMT

Hi,

I need the counts to calculate the % of data with default_flag=1.

So sub-setting of data would not help.

Re: how to count the number of observations that meet certain conditions using proc tabulate

ballardw — Wed, 10 Jan 2018 16:12:40 GMT

Assuming your formats work as intended and you want a table only related to the default_flag=1 then I would start with:

proc tabulate data=test;
   where default_flag=1;
   format LTV ltv_fmt. FICO fico_fmt. ;
   class FICO LTV;

   table FICO ,
         LTV*(N="count" PCTN="%");
run;

or possibly to get one table for each level of default_flag

proc tabulate data=test;
   class  default_flag;
   format LTV ltv_fmt. FICO fico_fmt. ;
   class FICO LTV;

   table default_flag,
         FICO ,
         LTV*(N="count" PCTN="%");
run;

BTW formats that you cannot tell what the actual end points are such as

'Medium(710,750)' and 'High(750,755)' are very problematic. Which category does a score of exactly 750 fit into? Not obvious from the displayed text.

Re: how to count the number of observations that meet certain conditions using proc tabulate

ballardw — Wed, 10 Jan 2018 17:19:36 GMT

@UshaLatha wrote:

Hi,

I need the counts to calculate the % of data with default_flag=1.

So sub-setting of data would not help.

Sum of Default_flag will give count of values =1 if default_flag is only coded 1/0.

If you want (count of default_flag = 1 for intersection of formatted LFV and FICO / total number of records in data set) as a percentage you will need that to be calculated outside of proc tabulate.

If you want (count of default_flag = 1 for intersection of formatted LFV and FICO / total number of default_flag = 1 in data set) as a percentage then you would want to make default flag a class variable with pctn but that would be also likely violate some version OR SUBSET the data to default_flag=1 with a where statement and N and PCTN for the other class variables on the table statement.

You might have to more completely describe you numerator and denominators for the percentage if it is something else you want for a percentage.

Or provide a small data set and show us what the result for that data set should be.