How to - Prevent Data Multiplexing

[ertr]

Hello everybody,

I have a sample data sets as below,

If a customer default within that month, then the ID variable are multiplying. I would like to get the highest default value. I mean that if my target_flg variable is 1 then I need to take the that row (value 1.) Can somebody help me about it, please?

Data Have;
Length ID 8 YearMonth 8 Date 8 Target 8;
Infile Datalines Missover;
Input ID YearMonth Date Target;
Format Date Date9.;
Datalines;
1 201410 20000 0
1 201410 20010 0
1 201410 20015 0
1 201410 20020 1
2 201410 20019 0
2 201410 20013 1
3 201410 20017 1
4 201410 20022 0
5 201410 20012 0
5 201410 20016 1
;

Desired Data set;

Thanks

[novinosrin · Accepted solution]

Data Have;
Length ID 8 YearMonth 8 Date 8 Target 8;
Infile Datalines Missover;
Input ID YearMonth Date Target;
Format Date Date9.;
Datalines;
1 201410 20000 0
1 201410 20010 0
1 201410 20015 0
1 201410 20020 1
2 201410 20019 0
2 201410 20013 1
3 201410 20017 1
4 201410 20022 0
5 201410 20012 0
5 201410 20016 1
;

proc sql;
select *
from have
group by id
having target=max(target)
order by id,target;
quit;

[novinosrin]

The above assumes GOOD customers do not have duplicate records

And does your default ID always have 1 in the last record  as neatly sorted as your sample suggests?

if yes

data want;
set have;
by id;
if last.id;
run;

should suffice