topic Re: top row by group with flag in SAS Programming

top row by group with flag

Nasser_DRMCP — Wed, 13 Oct 2021 10:11:57 GMT

data have;
input idt_ixn tsp_deb datetime18. top_dec $ ;
informat tsp_deb datetime18.;
format tsp_deb datetime18. ;
datalines ;
123 10NOV2020:10:10:10 0
123 10NOV2020:11:11:11 1
123 10NOV2020:12:12:12 1
456 11NOV2020:10:10:10 0
456 12NOV2020:11:11:11 0
456 13NOV2020:12:12:12 0
;

hello,

I would like tocreate a flag with value "1" only on the first top_dec =1 of the idt_ixn or on the first idt_ixn when there is no top_dec=1

in this case,

for the group idt_ixn=123, the flag should be 1 only where tsp_deb is 10NOV2020:11:11:11

for the group idt_ixn=456, the flag should be 1 only where tsp_deb is 11NOV2020:10:10:10

thnaks in advance

kind regards

Nass

Re: top row by group with flag

AMSAS — Wed, 13 Oct 2021 10:34:21 GMT

Check out FIRST. and LAST. Data Step Variables
That's how you can do this
EDIT - Should have read the post more carefully, not as simple as it first appears

Re: top row by group with flag

Kurt_Bremser — Wed, 13 Oct 2021 10:33:35 GMT

Its a little more tricky than just using FIRST., but this does it:

data have;
input idt_ixn tsp_deb datetime18. top_dec $ ;
informat tsp_deb datetime18.;
format tsp_deb datetime18. ;
datalines ;
123 10NOV2020:10:10:10 0
123 10NOV2020:11:11:11 1
123 10NOV2020:12:12:12 1
456 11NOV2020:10:10:10 0
456 12NOV2020:11:11:11 0
456 13NOV2020:12:12:12 0
;

data want;
merge
  have
  have (
    in=h1
    keep=idt_ixn top_dec
    rename=(top_dec=_top)
    where=(_top = "1")
  )
;
by idt_ixn;
__top = lag(top_dec);
flag = 0;
if first.idt_ixn and not h1 then flag = 1;
if h1 and top_dec = "1" and (first.idt_ixn or __top ne "1") then flag = 1;
drop _top __top;
run;