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 more