DATA Step, Macro, Functions and more

How to Count data for every customer?

Accepted Solution Solved
Reply
Regular Learner
Posts: 1
Accepted Solution

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: 261

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;

 

View solution in original post


All Replies
Super User
Posts: 5,097

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: 261

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 205 views
  • 1 like
  • 3 in conversation