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 |
Mainly your code may work without any syntax error, assuming the xlsx
worksheet was imported correctly to a sas dataset.
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/
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.