I want to do a cohort analysis, this is the result I want:
ID DATE PURCHASE COHORT_DATE
12 2018-01-01 1 2018Q1
12 2018-02-03 2 2018Q1
15 2018-10-09 1 2018Q4
25 2019-04-02 1 2019Q2
37 2018-02-02 1 2018Q1
37 2019-05-14 2 2018Q1
I use this code:
data cohort1;
set cohort;
purchase+1;
if first.id then purchase = 1;
if purchase=1 then cohort_date=date;
by id;
format cohort_date yyq.;
run;
And now it looks like this:
ID DATE PURCHASE COHORT_DATE
12 2018-01-01 1 2018Q1
12 2018-02-03 2 .
15 2018-10-09 1 2018Q4
25 2019-04-02 1 2019Q2
37 2018-02-02 1 2018Q1
37 2019-05-14 2 .
QUESTION: How do I add the same cohort_date for all same id:s?
Ok. Use Retain Statement like this
data cohort;
input ID DATE :yymmdd10.;
format DATE yymmdd10.;
datalines;
12 2018-01-01
12 2018-02-03
15 2018-10-09
25 2019-04-02
37 2018-02-02
37 2019-05-14
;
data cohort1;
set cohort;
by id;
purchase+1;
if first.id then do;
purchase = 1;
cohort_date=date;
end;
retain cohort_date;
format cohort_date yyq.;
run;
When you say you want to add "the same" cohort date for all similar id's, what date do you want to add? The first date for each id?
I want to add the first cohort_date (based on first transaction date) for each id. Where there now is a "."
Ok. Use Retain Statement like this
data cohort;
input ID DATE :yymmdd10.;
format DATE yymmdd10.;
datalines;
12 2018-01-01
12 2018-02-03
15 2018-10-09
25 2019-04-02
37 2018-02-02
37 2019-05-14
;
data cohort1;
set cohort;
by id;
purchase+1;
if first.id then do;
purchase = 1;
cohort_date=date;
end;
retain cohort_date;
format cohort_date yyq.;
run;
Glad you found your answer 🙂
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.