Group by two columns and count time spent

[Nilani]

Hi, I have a dataset like the following.

data my_data;
input appno id movement_date Date9. movement_indicator $;
format movement_date Date9. ;
datalines;
1 10 21JUN2023 A
1 10 19JUL2023 D
2 20 01DEC2022 A
2 20 04APR2023 D
2 20 16JUN2023 A
2 20 26NOV2023 D
4 20 28DEC2022 A
4 20 18MAR2023 D
4 50 28DEC2022 A
4 50 18MAR2023 D
6 60 17APR2023 D
7 70 27JUL2023 A
8 80 27NOV2022 D
8 80 27MAR2023 A
8 80 27JUL2023 D
;
run;

Arrivals are marked as 'A' and departures are 'D'. I want to calculate the time spent by each appno and id. I just want to disregard any single arrivals, and single departures. Also, if the first movement is a departure in any group they should also be ignored. The following is what I tried. But, still struglling to remove single departures and first departures.

data want(drop=LAG);
format LAG DATETIME24.;
set my_data;
LAG=LAG(movement_date);
by appno id;
if movement_indicator = 'A' then dif=0;
if movement_indicator = 'D' then dif=intCK("days",LAG,movement_date);
run;

Thanks. 

[yabwon · Accepted solution]

/*
How about: 
 - filter data first
 - perform processing
?
*/

data have2;
  set my_data;
  by appno id;
  if not (first.ID=1 and 1=last.ID);
  if not (first.ID and movement_indicator="D");
  if not (last.ID and movement_indicator="A");
run;
proc print;
run;

data want;
  merge 
    have2(where=(movement_indicator="A") rename=(movement_date=date_arr) )
    have2(where=(movement_indicator="D") rename=(movement_date=date_dep) )
  ;
   diff = date_dep - date_arr;
run;
proc print;
run;

Bart

[Nilani]

Thanks so much

[Ksharp]

/*
Assuming your data have been sorted by DATE within APPNO.
*/
data my_data;
input appno id movement_date Date9. movement_indicator $;
format movement_date Date9. ;
datalines;
1 10 21JUN2023 A
1 10 19JUL2023 D
2 20 01DEC2022 A
2 20 04APR2023 D
2 20 16JUN2023 A
2 20 26NOV2023 D
4 20 28DEC2022 A
4 20 18MAR2023 D
4 50 28DEC2022 A
4 50 18MAR2023 D
6 60 17APR2023 D
7 70 27JUL2023 A
8 80 27NOV2022 D
8 80 27MAR2023 A
8 80 27JUL2023 D
9 80 24NOV2022 D
9 80 27NOV2022 A
9 80 27MAR2023 A
9 80 27JUL2023 D
;
run;
data temp;
 merge my_data 
 my_data(firstobs=2 keep=appno movement_indicator rename=(appno=_a movement_indicator=_m));
if (appno=_a and movement_indicator='A' and _m='D') or
   (appno=lag(appno) and lag(movement_indicator)='A' and movement_indicator='D' );
drop _:;
run;
data want;
 set temp;
 dif=dif(movement_date);
 if movement_indicator='A' then call missing(dif);
run;