BookmarkSubscribeRSS Feed
subrat1
Fluorite | Level 6

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

7 REPLIES 7
subrat1
Fluorite | Level 6
Yes, i tried proc compare but it does not give the output that is
required
Shmuel
Garnet | Level 18

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.

Tom
Super User Tom
Super User

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

kiranv_
Rhodochrosite | Level 12

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;
Reeza
Super User

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



 

ballardw
Super User

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 845 views
  • 0 likes
  • 6 in conversation