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

Bhargavi221 — Sat, 16 Feb 2019 04:21:48 GMT

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

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

Patrick — Sat, 16 Feb 2019 05:35:36 GMT

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;

topic Re: Need Help. To repeat the flag value at only max age of that level Only. in New SAS User

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

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