DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 58

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

I have two table as listed below:

first table:                                                               

PolCategorypremium
3g10
6g3
9h7
12g20
15g12
18g100

 

 

Second table:

PolCategoryPremium
3x4
6g2
102g 
15z12
12g15
18g200

 

 

I want my output to be :

PolCategorypremiumPolCategoryPremiumPremium_Difference
3g103x46
6g36g21
9h7     NANA          NA7
     NANA           NA102g1010
12g2012g155
15g1215z120
18g10018g200-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

Super User
Super User
Posts: 8,273

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

Did you try PROC COMPARE?

Contributor
Posts: 58

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

Yes, i tried proc compare but it does not give the output that is
required
Trusted Advisor
Posts: 1,848

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

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.

Super User
Super User
Posts: 8,273

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

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;

image.png

PROC Star
Posts: 549

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

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;
Super User
Posts: 23,998

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

[ Edited ]

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



 

Super User
Posts: 13,926

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


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.

Ask a Question
Discussion stats
  • 7 replies
  • 172 views
  • 0 likes
  • 6 in conversation