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
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.
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.
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.
%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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.