Creating cumulative count with a condition

[Renan_Crepaldi]

Hello!

I have a data set that contains the Ids of my customers and their registration's date, besides the date each one made their first purchase (if they did it, meaning there are null values on this column):

I wanted to group them by the year and month of registration (Example: all customers registered on January/2019) and make a cumulative count of how many months it took for the first purchase, starting from their registration month until now. So if a customer that registered on January/2019 made his first purchase on February/2019, I want to count him from this month forward.

The objective is to compare if the recent customers are taking longer to start buying.

I imagine that the final data set would be somethin like this:

My final objective is to make a graphic comparing the % of each batch, similar to the example below:

Anyone knows how I could develop a data set in these conditions?

Sorry if my question is not clear enough, and I am available to give more details if necessary.

Thanks!

[PaigeMiller]

It would be extremely helpful if you could provide actual SAS data set as  DATA step code, rather than this screen capture of the data in some other program. In particular, we need to know if the dates you are showing are actual SAS dates, or SAS date/times values, or something else. Until I have that, I'm going to avoid providing specific code ...

Nevertheless, the difference in months between registration date and first purchase date is computed easily enough by the INTCK function. Once you have the difference in months, PROC FREQ will allow you to obtain the counts as you have shown in your second table.

[Renan_Crepaldi]

Hi, Paige.

Thanks for replying! I tried to run the script from the link you've sent, but it returns an error.

Nonetheless, I've extracted the SAS data types of the columns from my data set:

Name	Type	Length	Format	Informat	Label
customerID	Numeric	8	11.	11.
registrationDate	Date	8	DDMMYY8.
firstPurchaseDate	Date	8	DDMMYY8.

Besides that, I made an extraction with 1000 random observations and attached it to the post.

Thanks!

[PaigeMiller · Accepted solution]

So, the INTCK function determines how many months, and PROC FREQ computes the number.

data have2;
    set have;
    months=intck('month',registrationdate,firstpurchasedate);
run;
proc freq data=have2;
    tables registrationdate*months/noprint list out=_counts_;
run;

[Renan_Crepaldi]

It worked!

Thank you very much, Paige.

Best regards,
Renan