topic how can I select next 90 days observation for an id with particular product ? in SAS Programming

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

kksas — Tue, 03 Mar 2020 21:13:34 GMT

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

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

PaigeMiller — Tue, 03 Mar 2020 21:29:46 GMT

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.

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

novinosrin — Tue, 03 Mar 2020 22:02:40 GMT


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;