Hi everyone, I hope you can assist me with the following question:
I want to roll up as data according to customer id and month. My data look like this:
Customer_id | Month | Product |
1 | Jan2010 | Savings |
1 | Jan2010 | Cheque |
1 | Jan2010 | Savings |
1 | Jan2010 | Morgage |
1 | Jan2010 | Savings |
2 | Jan2010 | Cheque |
2 | Jan2010 | Cheque |
1 | Feb2010 | Savings |
1 | Feb2010 | Morgage |
1 | Feb2010 | Savings |
2 | Feb2010 | Cheque |
2 | Feb2010 | Cheque |
2 | Feb2010 | Cheque |
2 | Feb2010 | Morgage |
1 | Apr2010 | Morgage |
1 | Apr2010 | Savings |
2 | Apr2010 | Savings |
I would like output that counts the number of different products per customer per month, like this:
Customer_id | Month | num_cheque | num_savings | num_morgage |
1 | Jan2010 | 1 | 3 | 1 |
2 | Jan2010 | 2 | 0 | 0 |
1 | Feb2010 | 0 | 2 | 1 |
2 | Feb2010 | 3 | 0 | 1 |
1 | Apr2010 | 0 | 1 | 1 |
2 | Apr2010 | 0 | 1 | 0 |
You can coerce all the missing values to zero. This should do it, though.
proc sort data = have;
by customer_id month;
run;
proc freq data = have noprint;
by customer_id month;
table product / out = have_sum;
run;
proc transpose data = have_sum out = want (drop = _:) prefix = num_;
by customer_id month;
id product;
var count;
run;
PROC REPORT does this:
data have;
input Customer_id $ Month :monyy7. Product $;
format month yymmd7.;
datalines;
1 Jan2010 Savings
1 Jan2010 Cheque
1 Jan2010 Savings
1 Jan2010 Morgage
1 Jan2010 Savings
2 Jan2010 Cheque
2 Jan2010 Cheque
1 Feb2010 Savings
1 Feb2010 Morgage
1 Feb2010 Savings
2 Feb2010 Cheque
2 Feb2010 Cheque
2 Feb2010 Cheque
2 Feb2010 Morgage
1 Apr2010 Morgage
1 Apr2010 Savings
2 Apr2010 Savings
;
options missing = 0;
proc report data=have;
column month customer_id product;
define month / group;
define customer_id / group;
define product / across;
run;
data have;
input Customer_id $ Month :monyy7. Product $;
format month yymmd7.;
datalines;
1 Jan2010 Savings
1 Jan2010 Cheque
1 Jan2010 Savings
1 Jan2010 Morgage
1 Jan2010 Savings
2 Jan2010 Cheque
2 Jan2010 Cheque
1 Feb2010 Savings
1 Feb2010 Morgage
1 Feb2010 Savings
2 Feb2010 Cheque
2 Feb2010 Cheque
2 Feb2010 Cheque
2 Feb2010 Morgage
1 Apr2010 Morgage
1 Apr2010 Savings
2 Apr2010 Savings
;
proc sql;
create table want as
select Customer_id , Month ,
sum(Product = 'Cheque') as Cheque,
sum(Product = 'Savings') as Savings,
sum(Product = 'Morgage') as Morgage
from have
group by Customer_id , Month
order by 2,1;
quit;
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.