topic Creating a 'default-ever' flag in SAS Programming

Creating a 'default-ever' flag

mpn — Wed, 23 Feb 2022 16:51:37 GMT

Hi there, this is my first post. I apologise if this has previously been answered elsewhere but I could not find it.

I am looking to create a 'default-ever' flag that will be positive if an account has ever defaulted. Below is a dataset with an example of the variable I'm looking to create:

Please note that I'm looking for this 'default-ever' flag to be account specific.

Thanks in advance for your help 😀

Account	Date	Default	Default_Ever (what I want to make)
1	2015	0	1
1	2016	0	1
1	2017	0	1
1	2018	1	1
2	2014	0	0
2	2015	0	0
3	2016	0	1
3	2017	0	1
3	2018	1	1
3	2019	1	1

Re: Creating a 'default-ever' flag

Reeza — Wed, 23 Feb 2022 17:01:11 GMT

Add the max value of default per group in to the records.

proc sql;
create table want as
select *, max(default) as default_ever
from have
group by Account
order by 1, 2;
quit;

Other approaches:

Sort by Account and descending default and use RETAIN
Calculate maximum via data step and merge it in
DoW loop to merge in maximum as well

@mpn wrote:

Hi there, this is my first post. I apologise if this has previously been answered elsewhere but I could not find it.

I am looking to create a 'default-ever' flag that will be positive if an account has ever defaulted. Below is a dataset with an example of the variable I'm looking to create:

Please note that I'm looking for this 'default-ever' flag to be account specific.

Thanks in advance for your help 😀

Account Date Default Default_Ever (what I want to make)

1
2015 0 1

1 2016 0 1

1 2017 0 1

1 2018 1 1

2 2014 0 0

2 2015 0 0

3 2016 0 1

3 2017 0 1

3 2018 1 1

3 2019 1 1

Re: Creating a 'default-ever' flag

PeterClemmensen — Wed, 23 Feb 2022 18:45:44 GMT

data have;
input Account Date Default;
datalines;
1 2015 0 
1 2016 0 
1 2017 0 
1 2018 1 
2 2014 0 
2 2015 0 
3 2016 0 
3 2017 0 
3 2018 1 
3 2019 1 
;

data want;
   do until (last.Account);
      set have;
      by Account;
      Default_Ever = max(Default_Ever, Default);
   end;
   
   do until (last.Account);
      set have;
      by Account;
      output;
   end;
run;

Re: Creating a 'default-ever' flag

mpn — Thu, 24 Feb 2022 10:41:01 GMT

This worked perfectly, thank you.