Not sure of my solution

catkat96 — Wed, 01 Jul 2020 17:39:55 GMT

Hi everyone, I'm currently doing a basic training in SAS but unfortunately wasn't provided with the solutions, and there is one problem that I am unsure if I solved properly.

I uploaded a sample of the data I'm working with.

I learnt sorting, tables, merging, transposing, summarizing, proc freq, formats and macros, so you know around what level I am.

The task was to "produce a breakdown of defaulted customers vs non-defualted customers who took a loan in 2018 by demographic.
All available demographics should be used produce a file that gives a % in defualt for each subcategory in each wider category."

This was what I came up with:

data data_2018;
	set data.master(where=('01JAN2019'd>Disbursal_date>='01JAN2018'd));
run;

%MACRO dofreq2 (var);
	PROC freq DATA=data_2018;
		table &var.*latest_status / out=freq2_&var.;
	RUN;
%MEND;

%dofreq2(age_band);
%dofreq2(residential_status);

In your opinion, is this the right way to solve this (given the tools i've learned)?

I wasn't sure how to make a table with JUST the "default" percentages. Also, I wasn't sure if they meant the percentages of Default per age band (for example) out of the different percentages all status for all age bands have (which is what I did) or to take an age band as 100% and then get the percentage of defaults over that band only. How do you see it?

EDIT: here is the sample of data

Disbursal_date	Latest_Status	age_band	residential_status	Employment_status	loan_purpose	MISSING_POSTCODE
05/30/2016	Completed	41-60	Homeowner (Joint Mor	Full Time	Home Improvement	N
07/25/2018	Active	41-60	Homeowner (Mortgage)	Full Time	Home Improvement	N
11/14/2018	Completed	41-60	Homeowner (Joint Mor	Full Time	Home Improvement	N
05/13/2010	Completed	41-60	Homeowner (Joint Mor	Full Time	Home Improvement	N
02/10/2020	Active	41-60	Homeowner (No Mortga	Self Employed	Home Improvement	N
12/15/2014	Completed	41-60	Homeowner (Mortgage)	Full Time	Home Improvement	N
03/29/2016	Completed	41-60	Homeowner (Joint Mor	Full Time	Home Improvement	N
03/10/2020	Active	60+	Renting	Full Time	Home Improvement	N
05/31/2018	Completed	60+	Homeowner (No Mortga	Part Time	Home Improvement	N
11/27/2018	Active	60+	Homeowner (Mortgage)	Full Time	Home Improvement	N
12/11/2017	Active	60+	Living with Parents	Full Time	Home Improvement	N
07/18/2016	Completed	60+	Homeowner (Mortgage)	Full Time	Home Improvement	N
09/19/2016	Completed	60+	Homeowner (Joint Mor	Full Time	Home Improvement	N
10/15/2018	Completed	60+	Homeowner (Joint Mor	Full Time	Home Improvement	N
04/01/2018	Active	60+	Homeowner (Joint Mor	Full Time	Home Improvement	N
01/23/2012	Completed	60+	Homeowner (No Mortga	Part Time	Home Improvement	N
06/24/2016	Completed	60+	Renting	Full Time	Home Improvement	N
03/21/2014	Completed	60+	Homeowner (Joint Mor	Full Time	Home Improvement	N
05/08/2017	Active	18-21	Homeowner (Joint Mor	Full Time	Other	N
09/30/2015	Default	18-21	Homeowner (Mortgage)	Full Time	Other	N
07/10/2017	Active	18-21	Homeowner (Mortgage)	Full Time	Other	N
09/25/2015	Completed	18-21	Homeowner (Joint Mor	Full Time	Other	N
05/15/2019	Late	18-21	Homeowner (Mortgage)	Full Time	Other	N
08/15/2012	Completed	18-21	Renting	Full Time	Other	N
09/05/2018	Active	22-30	Homeowner (Joint Mor	Full Time	Other	N
09/11/2013	Completed	22-30	Homeowner (Mortgage)	Full Time	Other	N
10/31/2016	Completed	22-30	Homeowner (Joint Mor	Full Time	Other	N
06/19/2019	Active	22-30	Living with Parents	Full Time	Other	N
02/19/2018	Active	22-30	Homeowner (Joint Mor	Full Time	Other	N
04/12/2017	Completed	22-30	Renting	Full Time	Other	N
07/09/2018	Completed	22-30	Renting	Part Time	Other	N
03/06/2017	Completed	22-30	Living with Parents	Full Time	Other	N
04/07/2015	Default	22-30	Homeowner (Joint Mor	Full Time	Other	N
10/28/2017	Completed	22-30	Homeowner (No Mortga	Unemployed	Other	N
12/21/2015	Completed	22-30	Renting	Full Time	Other	N
02/19/2020	Active	31-40	Homeowner (Mortgage)	Full Time	Other	N
01/24/2020	Active	31-40	Homeowner (Joint Mor	Full Time	Other	N
01/09/2017	Completed	31-40	Homeowner (No Mortga	Full Time	Other	N
09/03/2018	Active	31-40	Renting	Full Time	Other	N
05/28/2012	Completed	31-40	Living with Parents	Full Time	Other	N
10/12/2017	Completed	31-40	Homeowner (Joint Mor	Full Time	Other	N
02/05/2018	Active	31-40	Homeowner (Joint Mor	Full Time	Other	N
08/30/2011	Completed	31-40	Homeowner (No Mortga	Part Time	Other	N
08/22/2019	Active	31-40	Homeowner (Mortgage)	Full Time	Other	N
11/01/2019	Active	31-40	Homeowner (Joint Mor	Full Time	Other	N
09/22/2018	Active	31-40	Homeowner (No Mortga	Full Time	Other	N
05/14/2016	Active	31-40	Homeowner (Mortgage)	Full Time	Other	N
11/14/2019	Active	31-40	Homeowner (Mortgage)	Full Time	Other	N
06/28/2016	Completed	31-40	Homeowner (No Mortga	Full Time	Other	N
09/12/2019	Active	31-40	Homeowner (Joint Mor	Full Time	Other	N
09/13/2017	Completed	31-40	Renting	Full Time	Other	N
10/16/2015	Completed	31-40	Homeowner (No Mortga	Full Time	Other	N
02/05/2020	Active	31-40	Homeowner (Mortgage)	Full Time	Other	N
09/20/2018	Completed	31-40	Renting	Self Employed	Other	N
05/15/2019	Completed	31-40	Living with Parents	Full Time	Other	N
02/22/2011	Completed	31-40	Renting	Full Time	Other	N
05/15/2019	Completed	31-40	Homeowner (Joint Mor	Full Time	Other	N
02/20/2019	Completed	31-40	Homeowner (Joint Mor	Contracting	Other	N
07/02/2018	Late	31-40	Renting	Part Time	Other	N
08/14/2017	Active	31-40	Renting	Contracting	Other	N
07/08/2016	Completed	31-40	Homeowner (Joint Mor	Part Time	Other	N
11/17/2019	Active	31-40	Homeowner (Mortgage)	Part Time	Other	N
02/17/2016	Completed	31-40	Homeowner (Joint Mor	Full Time	Other	N
06/10/2019	Default	31-40	Living with Parents	Part Time	Other	N
03/05/2018	Late	31-40	Homeowner (Joint Mor	Full Time	Other	N
05/06/2019	Active	31-40	Renting	Full Time	Other	N
03/06/2018	Late	31-40	Homeowner (Mortgage)	Full Time	Other	N
06/10/2013	Completed	31-40	Homeowner (Mortgage)	Full Time	Other	N
11/03/2015	Completed	41-60	Homeowner (Mortgage)	Part Time	Other	N
01/10/2012	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
06/10/2014	Completed	41-60	Homeowner (Mortgage)	Part Time	Other	N
10/08/2019	Completed	41-60	Homeowner (Mortgage)	Part Time	Other	N
02/17/2009	Completed	41-60	Renting	Part Time	Other	N
08/22/2016	Completed	41-60	Homeowner (No Mortga	Full Time	Other	N
04/20/2017	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
07/04/2018	Active	41-60	Living with Parents	Full Time	Other	N
12/03/2018	Active	41-60	Homeowner (Joint Mor	Full Time	Other	N
10/29/2015	Completed	41-60	Renting	Full Time	Other	N
06/06/2014	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
12/19/2016	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
10/25/2017	Active	41-60	Renting	Full Time	Other	N
04/23/2018	Active	41-60	Homeowner (No Mortga	Full Time	Other	N
06/24/2015	Default	41-60	Renting	Full Time	Other	N
11/06/2016	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
06/30/2017	Completed	41-60	Renting	Full Time	Other	N
09/16/2016	Completed	41-60	Homeowner (No Mortga	Full Time	Other	N
10/06/2016	Active	41-60	Renting	Full Time	Other	N
06/22/2019	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
11/22/2019	Active	41-60	Homeowner (Joint Mor	Full Time	Other	N
11/28/2016	Completed	41-60	Homeowner (Joint Mor	Part Time	Other	N
07/23/2019	Active	41-60	Homeowner (Mortgage)	Self Employed	Other	N
11/28/2019	Completed	41-60	Homeowner (No Mortga	Full Time	Other	N
06/23/2015	Active	41-60	Homeowner (Mortgage)	Full Time	Other	N
07/27/2018	Active	41-60	Renting	Unemployed	Other	N
07/27/2019	Completed	41-60	Living with Parents	Full Time	Other	N
01/31/2020	Active	41-60	Homeowner (No Mortga	Full Time	Other	N
12/27/2017	Completed	41-60	Homeowner (Mortgage)	Full Time	Other	N
09/17/2018	Active	41-60	Homeowner (Mortgage)	Full Time	Other	N
03/28/2017	Completed	41-60	Homeowner (Joint Mor	Full Time	Other	N
08/21/2016	Completed	41-60	Homeowner (Mortgage)	Unemployed	Other	N
09/30/2015	Default	41-60	Renting	Full Time	Other	N
10/14/2019	Active	41-60	Homeowner (No Mortga	Full Time	Other	N
06/26/2019	Active	41-60	Homeowner (Joint Mor	Full Time	Other	N
12/21/2015	Completed	41-60	Homeowner (Mortgage)	Self Employed	Other	N
05/21/2018	Active	41-60	Homeowner (Mortgage)	Full Time	Other	N
07/03/2018	Active	60+	Renting	Full Time	Other	N
11/02/2017	Completed	60+	Homeowner (Joint Mor	Full Time	Other	N
01/13/2015	Default	60+	Homeowner (No Mortga	Full Time	Other	N
10/10/2019	Active	18-21	Homeowner (Joint Mor	Unemployed	Car L	N
02/16/2019	Active	18-21	Homeowner (No Mortga	Full Time	Car L	N
09/04/2019	Active	22-30	Renting	Full Time	Car L	N
11/14/2019	Active	22-30	Renting	Full Time	Car L	N
03/07/2019	Active	31-40	Homeowner (No Mortga	Unemployed	Car L	N
07/27/2019	Active	31-40	Homeowner (Joint Mor	Full Time	Car L	N
09/08/2019	Active	31-40	Renting	Full Time	Car L	N
11/29/2019	Active	41-60	Renting	Full Time	Car L	N
02/28/2019	Default	41-60	Renting	Full Time	Car L	N
01/28/2019	Active	41-60	Homeowner (Joint Mor	Full Time	Car L	N
09/05/2019	Active	41-60	Homeowner (No Mortga	Full Time	Car L	N
11/08/2017	Completed	18-21	Renting	Full Time	Car Loan	N
06/04/2018	Active	18-21	Renting	Full Time	Car Loan	N
09/22/2017	Active	18-21	Homeowner (No Mortga	Full Time	Car Loan	N
02/25/2016	Completed	18-21	Homeowner (Mortgage)	Self Employed	Car Loan	N
02/01/2013	Completed	18-21	Homeowner (Joint Mor	Full Time	Car Loan	N
09/13/2017	Completed	18-21	Homeowner (Joint Mor	Full Time	Car Loan	N
12/06/2016	Completed	22-30	Living with Parents	Full Time	Car Loan	N
11/07/2016	Active	22-30	Homeowner (No Mortga	Full Time	Car Loan	N
06/06/2014	Completed	22-30	Homeowner (Joint Mor	Full Time	Car Loan	N
07/25/2018	Late	22-30	Homeowner (Mortgage)	Full Time	Car Loan	N
06/09/2016	Completed	22-30	Homeowner (Joint Mor	Full Time	Car Loan	N
02/11/2018	Completed	22-30	Homeowner (No Mortga	Full Time	Car Loan	N
11/25/2013	Completed	22-30	Homeowner (Joint Mor	Full Time	Car Loan	N
06/28/2016	Completed	22-30	Renting	Part Time	Car Loan	N
03/15/2017	Completed	22-30	Homeowner (Mortgage)	Full Time	Car Loan	N
04/06/2011	Completed	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
10/25/2012	Completed	31-40	Renting	Full Time	Car Loan	N
04/29/2015	Completed	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
08/02/2018	Active	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
08/26/2015	Completed	31-40	Living with Parents	Full Time	Car Loan	N
04/12/2011	Completed	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
02/12/2018	Late	31-40	Living with Parents	Full Time	Car Loan	N
08/18/2016	Completed	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
02/27/2016	Completed	31-40	Renting	Full Time	Car Loan	N
01/09/2020	Active	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
07/18/2017	Active	31-40	Homeowner (Mortgage)	Full Time	Car Loan	N
03/21/2016	Default	31-40	Homeowner (No Mortga	Full Time	Car Loan	N
01/24/2018	Active	31-40	Homeowner (No Mortga	Full Time	Car Loan	N
10/10/2016	Default	31-40	Renting	Part Time	Car Loan	N
03/09/2017	Default	31-40	Homeowner (Joint Mor	Part Time	Car Loan	N
03/10/2015	Completed	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
05/09/2016	Completed	31-40	Homeowner (No Mortga	Full Time	Car Loan	N
10/23/2014	Completed	31-40	Homeowner (Joint Mor	Full Time	Car Loan	N
05/30/2016	Completed	31-40	Homeowner (No Mortga	Full Time	Car Loan	N
05/20/2013	Completed	31-40	Homeowner (No Mortga	Full Time	Car Loan	N
09/13/2017	Completed	41-60	Homeowner (Mortgage)	Full Time	Car Loan	N
03/30/2017	Completed	41-60	Homeowner (Mortgage)	Full Time	Car Loan	N
09/22/2017	Default	41-60	Homeowner (Joint Mor	Full Time	Car Loan	N
01/22/2013	Completed	41-60	Living with Parents	Part Time	Car Loan	N
11/12/2018	Completed	41-60	Renting	Full Time	Car Loan	N
04/28/2016	Completed	41-60	Homeowner (Joint Mor	Full Time	Car Loan	N
11/23/2015	Completed	41-60	Homeowner (No Mortga	Full Time	Car Loan	N
09/03/2018	Active	41-60	Living with Parents	Full Time	Car Loan	N
03/28/2014	Completed	41-60	Homeowner (Joint Mor	Part Time	Car Loan	N
11/18/2016	Completed	41-60	Renting	Full Time	Car Loan	N
07/04/2016	Active	41-60	Homeowner (No Mortga	Full Time	Car Loan	N
09/22/2017	Default	41-60	Homeowner (Mortgage)	Part Time	Car Loan	N
10/31/2011	Completed	41-60	Living with Parents	Full Time	Car Loan	N
07/18/2018	Completed	41-60	Renting	Full Time	Car Loan	N
12/11/2018	Active	60+	Homeowner (Joint Mor	Full Time	Car Loan	N
07/17/2017	Active	60+	Homeowner (Joint Mor	Full Time	Car Loan	N
08/09/2016	Completed	60+	Renting	Full Time	Car Loan	N
03/15/2019	Active	18-21	Homeowner (No Mortga	Full Time	Debt	N
06/12/2019	Completed	18-21	Living with Parents	Full Time	Debt	N
09/06/2019	Active	18-21	Renting	Self Employed	Debt	N
05/09/2019	Active	18-21	Homeowner (Joint Mor	Self Employed	Debt	N
01/04/2019	Active	22-30	Homeowner (Joint Mor	Full Time	Debt	N
07/08/2019	Completed	31-40	Living with Parents	Full Time	Debt	N
10/11/2019	Active	41-60	Homeowner (No Mortga	Full Time	Debt	N
08/05/2019	Completed	41-60	Homeowner (No Mortga	Full Time	Debt	N
02/05/2019	Completed	60+	Homeowner (Joint Mor	Full Time	Debt	N
03/24/2016	Completed	18-21	Homeowner (Joint Mor	Contracting	Debt Consoliditation	N
06/12/2018	Completed	18-21	Homeowner (Mortgage)	Full Time	Debt Consoliditation	N
03/22/2011	Completed	18-21	Renting	Full Time	Debt Consoliditation	N
07/21/2017	Completed	22-30	Homeowner (Joint Mor	Full Time	Debt Consoliditation	N
12/04/2018	Active	22-30	Homeowner (Mortgage)	Full Time	Debt Consoliditation	N
08/09/2017	Completed	22-30	Renting	Full Time	Debt Consoliditation	N
12/15/2015	Completed	22-30	Homeowner (Joint Mor	Part Time	Debt Consoliditation	N
06/20/2017	Active	31-40	Living with Parents	Part Time	Debt Consoliditation	N
05/24/2017	Completed	31-40	Homeowner (Mortgage)	Self Employed	Debt Consoliditation	N

Re: Not sure of my solution

Shmuel — Wed, 01 Jul 2020 17:16:27 GMT

Mainly your code may work without any syntax error, assuming the xlsx
worksheet was imported correctly to a sas dataset.

Re: Not sure of my solution

PaigeMiller — Wed, 01 Jul 2020 17:18:24 GMT

Using PROC FREQ seems like a reasonable approach.

Most of us will not download or open Microsoft Office documents as they are a security threat. So I didn't look at your data, and can't specifically answer your question. To provide us the data in a more usable form, please follow these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

Re: Not sure of my solution

catkat96 — Wed, 01 Jul 2020 17:42:11 GMT

Okay! Tbh I couldn't work that out but I copied the sample and pasted it as a table... I hope it's good enough.
I used proc freq because this task was in that topic's program. 🙂

Re: Not sure of my solution

catkat96 — Wed, 01 Jul 2020 17:42:40 GMT

Yes! It does work without errors, what I'm trying to see if the logic is correct or not 🙂

topic Re: Not sure of my solution in SAS Programming

Not sure of my solution

Re: Not sure of my solution

Re: Not sure of my solution

Re: Not sure of my solution

Re: Not sure of my solution