topic count the policy no that are common in both table based on categories, I also want to count the poli in SAS Programming

count the policy no that are common in both table based on categories, I also want to count the poli

subrat1 — Sat, 03 Feb 2018 20:01:57 GMT

There are two table , I want to count the policy no that are common in both table based on categories, I also want to count the policy no that are present in one table and not present in another table, as shown below:

First Table: Second Table:

Pol	Category	Pol	Category
3	g	3	x
6	g	6	g
9	g	102	g
12	g	12	g
15	g	15	g
18	g	18	g
1	x	1	x
4	x	4	x
7	x	7	g
10	x	10	x
13	x	13	x
16	x	16	x
2	y	21	y
5	y	5	y
8	y	8	y
11	y	11	y
14	y	14	y
17	y	17	y

I want my output/result to be :

Re: count the policy no that are common in both table based on categories, I also want to count the

kiranv_ — Sat, 03 Feb 2018 20:53:44 GMT

there could be simpler way to do this. This is one way to do this

data have1;
infile datalines dsd  DLM='09'x;
input Pol	Category $1.;
datalines;	 	 	 
3	g
6	g
9	g 
12	g
15	g
18	g
1	x	 	 	 	
4	x
7	x
10	x
13	x
16	x
2	y
5	y
8	y
11	y
14	y
17	y
;

data have2;
infile datalines dsd  DLM='09'x;
input Pol	Category $1.;
datalines;
3	x
6	g
102	g
12	g
15	g
18	g
1	x
4	x
7	g
10	x
13	x
16	x
21	y
5	y
8	y
11	y
14	y
17	y
;


proc sql;
create table want as 
select coalesce(x.category, y.category, z.category) as category
, intersection, only_table1, only_table2 from 
(select a.category, count(a.category) as intersection
from have1 as a
inner join 
have2 as b
on a.pol = b.pol
and a.category = b.category
group by a.category)x
full join
(select a.category, count(a.category) as only_table1
from have1 as a
left  join 
have2 as b
on a.pol = b.pol
and a.category = b.category
where b.pol = .
group by a.category)y
on x.category =y.category
full join 
(select b.category, count(b.category) as only_table2
from have1 as a
right  join 
have2 as b
on a.pol = b.pol
and a.category = b.category
where a.pol = .
group by b.category)z
on x.category =z.category;

Re: count the policy no that are common in both table based on categories, I also want to count the

novinosrin — Sat, 03 Feb 2018 21:13:15 GMT

/*creating one and two datasets*/
data one two;
do _n_=1 to 2;
input Pol	Category $ @@;	
if _n_=1 then output one;
else output two;
end;
datalines; 
3	g	 	 	 	3	x
6	g	 	 	 	6	g
9	g	 	 	 	102	g
12	g	 	 	 	12	g
15	g	 	 	 	15	g
18	g	 	 	 	18	g
1	x	 	 	 	1	x
4	x	 	 	 	4	x
7	x	 	 	 	7	g
10	x	 	 	 	10	x
13	x	 	 	 	13	x
16	x	 	 	 	16	x
2	y	 	 	 	21	y
5	y	 	 	 	5	y
8	y	 	 	 	8	y
11	y	 	 	 	11	y
14	y	 	 	 	14	y
17	y	 	 	 	17	y
;

proc sort data=one;
by category pol;
run;

proc sort data=two;
by category pol;
run;


data want;
merge one(in=a) two(in=b);
by  category pol;
if first.category then call missing(intersection,only_table1,only_table2);
if a and not b then only_table1+1;
else if a and b then intersection+1;
else if not a and b then only_table2+1;
if last.category;
drop pol ;
run;

Re: count the policy no that are common in both table based on categories, I also want to count the

novinosrin — Sun, 04 Feb 2018 21:35:09 GMT

Proc sql:

data one two;
do _n_=1 to 2;
input Pol	Category $ @@;	
if _n_=1 then output one;
else output two;
end;
datalines; 
3	g	 	 	 	3	x
6	g	 	 	 	6	g
9	g	 	 	 	102	g
12	g	 	 	 	12	g
15	g	 	 	 	15	g
18	g	 	 	 	18	g
1	x	 	 	 	1	x
4	x	 	 	 	4	x
7	x	 	 	 	7	g
10	x	 	 	 	10	x
13	x	 	 	 	13	x
16	x	 	 	 	16	x
2	y	 	 	 	21	y
5	y	 	 	 	5	y
8	y	 	 	 	8	y
11	y	 	 	 	11	y
14	y	 	 	 	14	y
17	y	 	 	 	17	y
;

proc sql;
create table want as
select A.*,only_table1,only_table2
from
(select category,count(category) as intersection
from (select  * from one intersect select * from two)
group by category) a
full join 
(select category,count(category) as only_table1
from (select * from one  except select * from two)
group by category) b
on a.category=b.category
full join 
(select category,count(category) as only_table2
from (select * from two  except select * from one)
group by category) c
on  b.category=c.category;
quit;