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 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.