topic Re: Put the first date as cohort dates for all id:s in SAS Programming

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

SURIM — Mon, 03 Jun 2019 06:07:20 GMT

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?

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

PeterClemmensen — Mon, 03 Jun 2019 06:09:32 GMT

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?

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

SURIM — Mon, 03 Jun 2019 06:11:51 GMT

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

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

PeterClemmensen — Mon, 03 Jun 2019 06:17:29 GMT

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;

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

SURIM — Mon, 03 Jun 2019 06:29:30 GMT

Ahh, that's it. Thanks a million!

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

PeterClemmensen — Mon, 03 Jun 2019 07:12:41 GMT

Glad you found your answer 🙂