topic Re: Fetch customers list having one transaction per month in SAS Programming

Fetch customers list having atleast one transaction per month

Brad39 — Sun, 07 May 2023 16:52:09 GMT

Hi All, I have a query below for which I am looking for a solution through sas and proc sql both.Could anyone please help here:

We have a sample data for a year with customers having transaction for different products. Customers may have multiple transaction in a month or no transaction in a month. We are require to fetch customers list who is having at least one transaction each month:

Sample Data:

Customer_ID Transaction_Date Transaction_Amount

123 01/01/2020 100

909 02/01/2020 250

123 02/01/2020 45

900 03/02/2020 789

123 05/02/2020 950

and so on

Re: Fetch customers list having one transaction per month

PaigeMiller — Sun, 07 May 2023 14:28:07 GMT

Your title and your text do not agree.

Your title says "having one transaction per month"

Your text says "having at least one transaction each month"

Which is correct?

In either case, please show us the desired output from the sample data you provided.

Re: Fetch customers list having one transaction per month

ballardw — Sun, 07 May 2023 16:31:44 GMT

I think you may have to include a range of dates as well as in "at least one transaction each month in year 2020" or "at least one transaction each month between Jan 2018 and Mar 2023" or some such. Other wise we do not know which is the first or last month to be considered. Consider: if you expect a result that includes Jan 2018 and there is no data for that then none of your customers would qualify.

Re: Fetch customers list having one transaction per month

Brad39 — Sun, 07 May 2023 16:53:29 GMT

condition is atleast one transaction per month. We just need to pull the customer ID's who are having atleast one transaction per month for an yearly data say 2020

Re: Fetch customers list having one transaction per month

Brad39 — Sun, 07 May 2023 16:54:37 GMT

I am considering an yearly data for this query. From Jan 2020 to December 2020.

Re: Fetch customers list having one transaction per month

PaigeMiller — Sun, 07 May 2023 17:20:32 GMT

I think we would need a much larger sample data set than the one you provided, one that has several customers and transactions for an entire year for some of those customers.

Repeating: please show us the desired output from the sample data you provided.

Re: Fetch customers list having one transaction per month

Kurt_Bremser — Mon, 08 May 2023 06:04:35 GMT

%let year = 2020;

proc sort
  data=have (where=(year(transaction_date) = &year.))
  out=sorted
;
by customer_id;
run;

data want;
set sorted;
by customer_id;
array m {12} _temporary_;
if first.customer_id then call missing(of m{*});
m{month(transaction_date)} = 1;
if last.customer_id and sum(of m{*}) = 12 then output;
keep customer_id;
run;