topic Re: How to Count data for every customer? in SAS Programming

How to Count data for every customer?

shrey0411 — Wed, 14 Jun 2017 08:22:23 GMT

Input Data:

cust_id channel

1 ATM

2 POS

1 POS

1 MOB

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.

Re: How to Count data for every customer?

Astounding — Wed, 14 Jun 2017 14:10:23 GMT

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.

Re: How to Count data for every customer?

kiranv_ — Wed, 14 Jun 2017 19:31:24 GMT

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;