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.
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.