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 |
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:
@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
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:
@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
This worked perfectly, thank you.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.