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
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;
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.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: