topic Comapare two table on observation wise and also calculate difference for numeric columns in SAS Programming

Comapare two table on observation wise and also calculate difference for numeric columns

subrat1 — Sat, 03 Feb 2018 20:22:54 GMT

I have two table as listed below:

first table:

Pol	Category	premium
3	g	10
6	g	3
9	h	7
12	g	20
15	g	12
18	g	100

Second table:

Pol	Category	Premium
3	x	4
6	g	2
102	g
15	z	12
12	g	15
18	g	200

I want my output to be :

Pol	Category	premium	Pol	Category	Premium	Premium_Difference
3	g	10	3	x	4	6
6	g	3	6	g	2	1
9	h	7	NA	NA	NA	7
NA	NA	NA	102	g	10	10
12	g	20	12	g	15	5
15	g	12	15	z	12	0
18	g	100	18	g	200	-100

Logic: If same pol is present in both table then put them in same row and calculate the difference, but if it present in one table then put NA for other table

Re: Comapare two table on observation wise and also calculate difference for numeric columns

Tom — Sat, 03 Feb 2018 20:34:47 GMT

Did you try PROC COMPARE?

Re: Comapare two table on observation wise and also calculate difference for numeric columns

subrat1 — Sat, 03 Feb 2018 21:00:51 GMT

Yes, i tried proc compare but it does not give the output that is
required

Re: Comapare two table on observation wise and also calculate difference for numeric columns

Shmuel — Sat, 03 Feb 2018 21:26:29 GMT

If the relation between the tables is 1:1 (no duplicate POL in any of the two tables) then merge

the tables by POL, rename variables of 2nd table and do compare the variables, one by one, in a datastep.

You may need create a foemat to show 'NA' instead a missing value.

Re: Comapare two table on observation wise and also calculate difference for numeric columns

Tom — Sat, 03 Feb 2018 22:29:34 GMT

If you don't like the standard PROC COMPARE output then use it to produce a difference dataset and reform that.

data one;
  input Pol Category $ premium @@;
cards;
3 g 10 6 g 3 9 h 7 12 g 20 15 g 12 18 g 100
;

data two ;
  input Pol Category $ premium @@;
cards;
3 x 4 6 g 2 102 g . 15 z 12 12 g 15 18 g 200
;

proc compare data=one compare=two noprint  out=diffs outall;
run;

proc transpose data=diffs out=tall ;
  where _type_ ne 'PERCENT';
  by _obs_ _type_;
  var pol category premium;
run;
proc transpose data=tall out=want(drop=_name_) delim=_;
  by _obs_  ;
  id _name_ _type_ ;
  var col1 ;
run;

Re: Comapare two table on observation wise and also calculate difference for numeric columns

kiranv_ — Sat, 03 Feb 2018 23:57:00 GMT

Another way

data have1;
infile datalines dsd  DLM='09'x;
input Pol	Category $	premium;
datalines;	 	 	 
3	g	10
6	g	3
9	h	7
12	g	20
15	g	12
18	g	100
 ;

data have2;
infile datalines dsd  DLM='09'x;
input Pol	Category $	premium;
datalines;
3	x	4
6	g	2
102	g	.
15	z	12
12	g	15
18	g	200
;

proc sql;
select 
case when a.pol is missing then 'NA' 
     else put(a.pol,8.)
end as pol,
case when a.category is missing 
     then 'NA' else a.category
end as category,
case when a.premium is missing 
     then 'NA' else put(a.premium,8.)
end as premium,
case when b.pol is missing 
     then 'NA' else put(b.pol,8.)
end as pol,
case when b.category is missing 
      then 'NA' else b.category
end as category,
case when b.premium is missing 
     then 'NA' else put(b.premium,8.)
end as premium

from have1 as a
full join 
have2 as b
on a.pol = b.pol;

Re: Comapare two table on observation wise and also calculate difference for numeric columns

Reeza — Sun, 04 Feb 2018 01:26:40 GMT

1. Sort your data

2. Rename your variables so that when they're merged they don't overwrite each other.

3. Merge

4. Calculate Difference and Assign NA to missing values.

Note that numeric variables can't have character values so you'll need a format or convert everything to characters. I've left that as an exercise to you.

proc sort data=one; 
by pol;

proc sort data=two;
by pol;
run;

data want;
merge one (rename=(Category = Cat1 Premium=Prem1)) /*rename*/
        two (rename=(Category = Cat2 Premium=Prem2));

by Pol;

Diff = Prem1 - Prem2;

run;

@subrat1 wrote:

I have two table as listed below:

first table:

Pol Category premium

3 g 10

6 g 3

9 h 7

12 g 20

15 g 12

18 g 100

Second table:

Pol Category Premium

3 x 4

6 g 2

102 g

15 z 12

12 g 15

18 g 200

I want my output to be :

Pol Category premium Pol Category Premium Premium_Difference

3 g 10 3 x 4 6

6 g 3 6 g 2 1

9 h 7      NA NA           NA 7

     NA NA            NA 102 g 10 10

12 g 20 12 g 15 5

15 g 12 15 z 12 0

18 g 100 18 g 200 -100

Logic: If same pol is present in both table then put them in same row and calculate the difference, but if it present in one table then put NA for other table

Re: Comapare two table on observation wise and also calculate difference for numeric columns

ballardw — Mon, 05 Feb 2018 16:45:10 GMT

@subrat1 wrote:

I have two table as listed below:

first table:

Pol Category premium

3 g 10

6 g 3

9 h 7

12 g 20

15 g 12

18 g 100

Second table:

Pol Category Premium

3 x 4

6 g 2

102 g

15 z 12

12 g 15

18 g 200

I want my output to be :

Pol Category premium Pol Category Premium Premium_Difference

3 g 10 3 x 4 6

6 g 3 6 g 2 1

9 h 7      NA NA           NA 7

     NA NA            NA 102 g 10 10

12 g 20 12 g 15 5

15 g 12 15 z 12 0

18 g 100 18 g 200 -100

Logic: If same pol is present in both table then put them in same row and calculate the difference, but if it present in one table then put NA for other table

You can only have one variable name POL, Category or Premium. Change the names in your output to clearly identify which data set contributes which value. Also if your variable premium in NUMERIC it cannot have the Value NA, It may have a value of MISSING, which you can display as NA but not the value.