🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: Put the first date as cohort dates for all 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;
``````
5 REPLIES 5
Tourmaline | Level 20

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

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?

Obsidian | Level 7

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

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

Tourmaline | Level 20

## Re: Put the first date as cohort dates for all 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;
``````
Obsidian | Level 7

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

Ahh, that's it. Thanks a million!
Tourmaline | Level 20