Solved
Regular Learner
Posts: 1

# How to Count data for every customer?

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.

Accepted Solutions
Solution
‎06-15-2017 12:54 AM
PROC Star
Posts: 509

## Re: How to Count data for every customer?

[ Edited ]

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;

All Replies
Super User
Posts: 6,753

## Re: How to Count data for every customer?

[ Edited ]

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.

Solution
‎06-15-2017 12:54 AM
PROC Star
Posts: 509

## Re: How to Count data for every customer?

[ Edited ]

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;

☑ This topic is solved.