Put the first date as cohort dates for all id:s

[SURIM]

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?

[PeterClemmensen]

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?

[SURIM]

I want to add the first cohort_date (based on first transaction date) for each id. Where there now is a "."

[PeterClemmensen · Accepted solution]

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;

[SURIM]

Ahh, that's it. Thanks a million!

[PeterClemmensen]

Glad you found your answer 🙂