topic Re: Identify transaction frequency of customer in SAS Programming

Identify transaction frequency of customer

aranganayagi — Tue, 26 Jun 2018 14:37:29 GMT

We need to find out the transaction frequency of customers. How many customers are doing atleast one transaction weekly once/ monthly once/ 2 months once, etc.can you please help on this.

Re: Identify transaction frequency of customer

ballardw — Tue, 26 Jun 2018 14:43:07 GMT

Provide example data in the form of a datastep and what the desired result is. Do you need a report that people read or a data set to feed another analysis process?

Since you are talking about date intervals the process will almost certainly work better if your date information is in the form of a SAS date valued variable an not character.

Re: Identify transaction frequency of customer

aranganayagi — Tue, 26 Jun 2018 15:41:11 GMT

Thanks for the reply. I want to use the dataset for further analysis.
Dataset: I have last 3 months transaction data. If customer is doing atleast one transaction every week
I want to put the freq as 'weekly once'. If he is doing some transaction two week once, then freq should be '2 weeks once
If he is doing transaction monthly once, then it should be 'monthly once',
' 2 months once' , ' 3 months once, etc.
If one month the frequency is monthly once and on another month, if it is 2 weeks once, the freq should be lowest one ' 2 weeks once.
Customer_Id txn_date txn_id
A 13/5/2018 01
A 12/6/2018 02
B. 1/6/2018. 03
B. 8/6/2018. 04
B 15/6/2018 05
C. 1/4/2018. 06

Now I want the output to be like
Customer_id txn_freq
A monthly once
B weekly once
C 2 months once.

Re: Identify transaction frequency of customer

error_prone — Tue, 26 Jun 2018 17:07:33 GMT

That's an interesting problem ... will have to think about it ... calculating diffs (=date_diff) between dates (in weeks) and setting txn_freq to the lowest value found for each customer_id ... formatting date_diff to show the expected text ... just some initial thoughts.

Re: Identify transaction frequency of customer

mkeintz — Tue, 26 Jun 2018 20:40:51 GMT

I see how A is once per month, and B is once per week, given the intervals between records. But there is no interval for C since it has only one record. So why is it termed "2 months once"?

Re: Identify transaction frequency of customer

aranganayagi — Tue, 26 Jun 2018 23:36:15 GMT

Hi Error_prone, Thanks for the idea.
Hi Mkeintz, You are correct. Dataset should look like below. For the above dataset,C's freq should be 3 months once.

A 13/5/2018 01
A 12/6/2018 02
B. 1/6/2018. 03
B. 8/6/2018. 04
B 15/6/2018 05
C. 1/4/2018. 06
C 1/6/2018. 07

Re: Identify transaction frequency of customer

mkeintz — Wed, 27 Jun 2018 13:21:32 GMT

State your rules.

You now have 2 C records, one in April and one in June. So how are we to know why you call this once per 3 months?

Please clearly state the criteria you use to infer each possible frequency. For an effective coding solution, a clear specification of the requirements is needed.