How to Count data for every customer?

[shrey0411]

Input Data:

cust_id   channel

1             ATM

2             POS

1             POS

1             MOB

2             POS

2             POS

OUTPUT Data:

CUST_ID  CHANNEL  FREQUENCY

1                ATM            1

1                POS            1

1                MOB           1

2                ATM            0

2                POS            3

2                MOB           0

THANK YOU.

[Astounding]

Here's an approach (although it's all untested):

proc sql;

create table customers as select distinct cust_id from have;

create table channels as select distinct channel from have;

create table shell as select * from customers, channels

order by cust_id, channel;

quit;

That gives you a shell with all the possible combinations of CUST_ID and CHANNEL.  Then count:

proc sort data=have;

by cust_id channel;

run;

data want;

merge shell have (in=count_this);

by cust_id channel;

if first.channel then frequency=0;

frequency + count_this;

if last.channel;

run;

There may be a simpler way to do this using PROC FREQ with the SPARSE option, and creating an output data set.  I'm just not as familiar with those tools.

[kiranv_ · Accepted solution]

Below thing will work, but may not be very efficient

data abc;
input id val $;
datalines;
1             ATM
2             POS
1             POS
1             MOB
2             POS
2             POS
; 
quit;
proc sql;
/*this part gives where you have value and combination*/
select id, val, count(*) as cnt from 
abc
group by 1,2
union 
/* gets you the count where id and value combination is missing*/
select y.*, 0 as cnt from 
(select * from 
 abc)x 
right join 
(select * from 
(select distinct id from abc)a
cross join 
(select distinct val from abc)b)y
on x.id = y.id
and x.val = y.val
where x.id is missing
and x.val is missing;
quit;

/*another similar but cleaner way below*/

/* step 1 get all the combinations*/

proc sql;

create table allcoombo as

select * from

(select distinct id from abc)a

cross join

(select distinct val from abc);

quit;

/*step2*/

proc sql;

/*this part gives where you have value and combination*/

select id, val, count(*) as cnt from

abc

group by 1,2

union

/* gets you the count where id and value combination is missing*/

select *, 0 as cnt from

allcoombo x

where not exists

(select id, val from

abc y

where x.id = y.id

and x.val = y.val);

quit;