Creating a 'default-ever' flag

[mpn]

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

[Reeza · Accepted solution]

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

---

[mpn]

This worked perfectly, thank you.

[PeterClemmensen]

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;