Need Help. To repeat the flag value at only max age of that level Only.

[Bhargavi221]

we need to look for max age, if max age fall under the level that level flag has 1 remaining levels for that flag has 0. based on date, id, level.

Data source;

input id$ Date level$ age;

cards;

A 2/16/2019 L1 9
A 2/16/2019 L3 25
A 2/16/2019 L1 10
A 2/16/2019 L3 22
A 2/16/2019 L1 5
A 2/16/2019 L2 15
A 2/16/2019 L2 11
B 2/17/2019 L1 4
B 2/17/2019 L2 12
B 2/17/2019 L3 30
B 2/17/2019 L4 45
B 2/17/2019 L4 47
B 2/17/2019 L3 23

;

RUN;

i want output:

ID    DATE        LEVEL   AGE   FLAG
A    2/16/2019      L1         9         0
A    2/16/2019      L3       25         1
A    2/16/2019      L1       10         0
A    2/16/2019      L3       22         1
A    2/16/2019      L1       5           0
A    2/16/2019      L2       15         0
A    2/16/2019      L2      11          0
B    2/17/2019      L1      4            0
B    2/17/2019      L2      12          0
B    2/17/2019      L3      30          0
B    2/17/2019      L4     45           1
B    2/17/2019      L4     47           1
B    2/17/2019      L3     23           0

[Patrick · Accepted solution]

proc sql;
  create table want as
    select 
      s.*, 
      coalescec(v.flag,'0') as flag
    from 
      source s
      left join
      (
        select distinct id,date,level, '1' as flag length=1
        from source
        group by id, date
        having max(age)=age
      ) v
      on s.id=v.id and s.date=v.date and s.level=v.level
    ;
quit;