Fetch customers list having atleast one transaction per month

[Brad39]

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 

[PaigeMiller]

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.

[Brad39]

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

[ballardw]

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.

[Brad39]

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

[PaigeMiller]

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.

[Kurt_Bremser]

%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;