how can I select next 90 days observation for an id with particular product ?

[kksas]

Hi, I can't find a way for a particular problem at my work.

In the below dataset I have ids , date on which they brought the product and another column for what product they bought.

I want all products used by each id in the next 90 days after buying product X.

Data

id    date                 PRD

01   01JAN2019       A

01   20FEB2019       X

01   28MAR2019      B

02   10JUN2019       X

02   02OCT2019      A

03   05JAN2019        A

03   22FEB2019        B

03   15MAR2019       X

03   29MAR2019      C

03   13APR2019       D

03   15AUG2019       E

Final Output:

id    date                 PRD

01   20FEB2019       X

01   28MAR2019      B

02   10JUN2019       X

03   15MAR2019       X

03   29MAR2019      C

03   13APR2019       D

[PaigeMiller]

UNTESTED CODE

data want;
    merge have(where=(prd='X') rename=(date=xdate)) have(where=(prd^='X'));
    by id;
    if date-xdate<=90 then output;
    drop xdate;
run;

Assumes data set is sorted by ID.

[novinosrin · Accepted solution]

data have;
 input id    date   :date9.              PRD $;
 format date date9.;
 cards;

01   01JAN2019       A

01   20FEB2019       X

01   28MAR2019      B

02   10JUN2019       X

02   02OCT2019      A

03   05JAN2019        A

03   22FEB2019        B

03   15MAR2019       X

03   29MAR2019      C

03   13APR2019       D

03   15AUG2019       E
;

data want;
 set have;
 by id;
 retain _d;
 if first.id then call missing(_d);
 if prd='X' then do; _d=date;output;end;
 else if _d then if _d<=date<=intnx('day',_d,90) then output;
 drop _d;
run;

/*or*/

data want;
 set have;
 by id;
 retain _d;
 if first.id then call missing(_d);
 if prd='X' then  _d=date;
 if _d then if _d<=date<=intnx('day',_d,90) then output;
 drop _d;
run;