topic Re: How to roll up data according to id and date%3F in SAS Programming

How to roll up data according to id and date%3F

Giraffe123 — Tue, 23 Feb 2021 13:16:44 GMT

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

Re: How to roll up data according to id and date%3F

maguiremq — Tue, 23 Feb 2021 14:20:30 GMT

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;

Re: How to roll up data according to id and date%3F

Kurt_Bremser — Tue, 23 Feb 2021 14:26:08 GMT

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;

Re: How to roll up data according to id and date%3F

Ksharp — Wed, 24 Feb 2021 11:55:34 GMT

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;