DATA Step, Macro, Functions and more

Compare two table and then find difference

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Compare two table and then find difference

[ Edited ]

I have two table created on weekly basis, I want to Compare two table and then find difference of distinct count SESSION_ID Account_Name EFFECTIVE_DT EXPIRY_DT DIVISION_NO Submission_no

Also I want to find the difference of Summation of Benchmark_Technical_Price

1st table: 

SESSION_IDAccount_NameEFFECTIVE_DTEXPIRY_DTDIVISION_NOSubmission_noBenchmark_Technical_Price
6138NITRO LIFT HOLDINGS2017-03-312017-12-312600000000054650
6180THE TURBULATOR COMPANY LLC2017-04-012018-02-01260080414095642
8277Snap-On Incorporated2018-01-012019-01-015900876750146795.2206
7372Kay Polymer Trucking2017-12-012018-12-014400883806531785.4632
4997ALSINA FORMS CO., INC2017-07-072018-07-074401269484928366.432
6145WATERMARK ESTATE MANAGEMENT2017-05-012018-05-01590197649849609
6137PLURIS HOLDINGS LLC2017-04-292018-04-29260206236079824

 

 

 

Second Table:

SESSION_IDAccount_NameEFFECTIVE_DTEXPIRY_DTDIVISION_NOSubmission_noBenchmark_Technical_Price
15926Talajak Inc2018-02-232019-02-232602363883425337.8853
17006Stallion Express, Inc. DBA Stallion Transportation Group2018-02-012019-02-01270243317872609.5322
18112LEARNING BITS, INC.2018-03-192019-03-19440269838081388.2046
13738PAULUCA PETROLEUM INC2018-02-032019-02-032602757086810752.537
13342DESIGNER GROUP USA INC.2018-02-012019-02-01440278782077958.3232
10300SANNER OF AMERICA, INC.2018-03-152019-01-01440285545141138.8927
14473BYFOD2018-02-152019-02-15440306974982671.9558
6495Hooper Trucking Ltd2017-10-012018-10-012603098775019811.1084
8339Luera's Welding Service, Inc.2017-11-292018-11-292603107137710752.537
16725EVOCA USA LLC2018-03-032019-03-03440318495847958.3232

 

 

 

I want my Result:

WeekSESSION_IDAccount_NameEFFECTIVE_DTEXPIRY_DTDIVISION_NOSubmission_noBenchmark_Technical_Price
Lastweek666636186672.1158
Thisweek88883890379.2994
Difference222202-96292.8164
% Difference25%25%25%25%0%25%-107%

Accepted Solutions
Solution
‎04-14-2018 11:22 AM
PROC Star
Posts: 626

Re: Compare two table and then find difference

If you want quick response from here please post your sample data in the form of a Data step. Here is the link how to provide sample data. Generate sample data.

 

Is your want result correct? There are 7 distinct session id's in first table and you mentioned as 6, Did you miss something?

 

Here is my version depending on how I understood your requirement. 


proc sql;
create table want as  
select 	"Lastweek" as Week,count(distinct SESSION_ID) as SESSION_ID,
		count(distinct Account_Name) as Account_Name, 
		count(distinct EFFECTIVE_DT) as EFFECTIVE_DT,
		count(distinct EXPIRY_DT) as EXPIRY_DT,
		count(distinct DIVISION_NO) as DIVISION_NO,
		count(distinct Submission_no) as Submission_no,
		SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price
	from bench11
union
select	"Thisweek" as Week,count(distinct SESSION_ID) as SESSION_ID,
		count(distinct Account_Name) as Account_Name, 
		count(distinct EFFECTIVE_DT) as EFFECTIVE_DT,
		count(distinct EXPIRY_DT) as EXPIRY_DT,
		count(distinct DIVISION_NO) as DIVISION_NO,
		count(distinct Submission_no) as Submission_no,
		SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price 
	from bench2
union 
select 	"Difference" as Week,t2.SESSION_ID_2-t1.SESSION_ID_1 as SESSION_ID,
		t2.Account_Name_2-t1.Account_Name_1 as Account_Name,
		t2.EFFECTIVE_DT_2-t1.EFFECTIVE_DT_1 as EFFECTIVE_DT,
		t2.EXPIRY_DT_2-t1.EXPIRY_DT_1 as EXPIRY_DT,
		t2.DIVISION_NO_2-t1.DIVISION_NO_1 as DIVISION_NO,
		t2.Submission_no_2-t1.Submission_no_1 as Submission_no,
		t2.Benchmark_Technical_Price_2-t1.Benchmark_Technical_Price_1 as Benchmark_Technical_Price
		
	from (select count(distinct SESSION_ID) as SESSION_ID_1,
				count(distinct Account_Name) as Account_Name_1, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_1,
				count(distinct EXPIRY_DT) as EXPIRY_DT_1,
				count(distinct DIVISION_NO) as DIVISION_NO_1,
				count(distinct Submission_no) as Submission_no_1,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_1
	from bench11) t1,
		 (select count(distinct SESSION_ID) as SESSION_ID_2,
				count(distinct Account_Name) as Account_Name_2, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_2,
				count(distinct EXPIRY_DT) as EXPIRY_DT_2,
				count(distinct DIVISION_NO) as DIVISION_NO_2,
				count(distinct Submission_no) as Submission_no_2,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_2 
	from bench2) t2
union
select 	"% Difference" as Week,
		(t2.SESSION_ID_2-t1.SESSION_ID_1)*100/t2.SESSION_ID_2 as SESSION_ID,
		(t2.Account_Name_2-t1.Account_Name_1)*100/t2.Account_Name_2 as Account_Name,
		(t2.EFFECTIVE_DT_2-t1.EFFECTIVE_DT_1)*100/t2.EFFECTIVE_DT_2 as EFFECTIVE_DT,
		(t2.EXPIRY_DT_2-t1.EXPIRY_DT_1)*100/t2.EXPIRY_DT_2 as EXPIRY_DT,
		(t2.DIVISION_NO_2-t1.DIVISION_NO_1)*100/t2.DIVISION_NO_2 as DIVISION_NO,
		(t2.Submission_no_2-t1.Submission_no_1)*100/t2.Submission_no_2 as Submission_no,
		(t2.Benchmark_Technical_Price_2-t1.Benchmark_Technical_Price_1)*100/t2.Benchmark_Technical_Price_2 as Benchmark_Technical_Price
		
	from (select count(distinct SESSION_ID) as SESSION_ID_1,
				count(distinct Account_Name) as Account_Name_1, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_1,
				count(distinct EXPIRY_DT) as EXPIRY_DT_1,
				count(distinct DIVISION_NO) as DIVISION_NO_1,
				count(distinct Submission_no) as Submission_no_1,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_1
	from bench11) t1,
		 (select count(distinct SESSION_ID) as SESSION_ID_2,
				count(distinct Account_Name) as Account_Name_2, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_2,
				count(distinct EXPIRY_DT) as EXPIRY_DT_2,
				count(distinct DIVISION_NO) as DIVISION_NO_2,
				count(distinct Submission_no) as Submission_no_2,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_2 
	from bench2) t2
order by 1 desc
;
quit;
Thanks,
Suryakiran

View solution in original post


All Replies
Solution
‎04-14-2018 11:22 AM
PROC Star
Posts: 626

Re: Compare two table and then find difference

If you want quick response from here please post your sample data in the form of a Data step. Here is the link how to provide sample data. Generate sample data.

 

Is your want result correct? There are 7 distinct session id's in first table and you mentioned as 6, Did you miss something?

 

Here is my version depending on how I understood your requirement. 


proc sql;
create table want as  
select 	"Lastweek" as Week,count(distinct SESSION_ID) as SESSION_ID,
		count(distinct Account_Name) as Account_Name, 
		count(distinct EFFECTIVE_DT) as EFFECTIVE_DT,
		count(distinct EXPIRY_DT) as EXPIRY_DT,
		count(distinct DIVISION_NO) as DIVISION_NO,
		count(distinct Submission_no) as Submission_no,
		SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price
	from bench11
union
select	"Thisweek" as Week,count(distinct SESSION_ID) as SESSION_ID,
		count(distinct Account_Name) as Account_Name, 
		count(distinct EFFECTIVE_DT) as EFFECTIVE_DT,
		count(distinct EXPIRY_DT) as EXPIRY_DT,
		count(distinct DIVISION_NO) as DIVISION_NO,
		count(distinct Submission_no) as Submission_no,
		SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price 
	from bench2
union 
select 	"Difference" as Week,t2.SESSION_ID_2-t1.SESSION_ID_1 as SESSION_ID,
		t2.Account_Name_2-t1.Account_Name_1 as Account_Name,
		t2.EFFECTIVE_DT_2-t1.EFFECTIVE_DT_1 as EFFECTIVE_DT,
		t2.EXPIRY_DT_2-t1.EXPIRY_DT_1 as EXPIRY_DT,
		t2.DIVISION_NO_2-t1.DIVISION_NO_1 as DIVISION_NO,
		t2.Submission_no_2-t1.Submission_no_1 as Submission_no,
		t2.Benchmark_Technical_Price_2-t1.Benchmark_Technical_Price_1 as Benchmark_Technical_Price
		
	from (select count(distinct SESSION_ID) as SESSION_ID_1,
				count(distinct Account_Name) as Account_Name_1, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_1,
				count(distinct EXPIRY_DT) as EXPIRY_DT_1,
				count(distinct DIVISION_NO) as DIVISION_NO_1,
				count(distinct Submission_no) as Submission_no_1,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_1
	from bench11) t1,
		 (select count(distinct SESSION_ID) as SESSION_ID_2,
				count(distinct Account_Name) as Account_Name_2, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_2,
				count(distinct EXPIRY_DT) as EXPIRY_DT_2,
				count(distinct DIVISION_NO) as DIVISION_NO_2,
				count(distinct Submission_no) as Submission_no_2,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_2 
	from bench2) t2
union
select 	"% Difference" as Week,
		(t2.SESSION_ID_2-t1.SESSION_ID_1)*100/t2.SESSION_ID_2 as SESSION_ID,
		(t2.Account_Name_2-t1.Account_Name_1)*100/t2.Account_Name_2 as Account_Name,
		(t2.EFFECTIVE_DT_2-t1.EFFECTIVE_DT_1)*100/t2.EFFECTIVE_DT_2 as EFFECTIVE_DT,
		(t2.EXPIRY_DT_2-t1.EXPIRY_DT_1)*100/t2.EXPIRY_DT_2 as EXPIRY_DT,
		(t2.DIVISION_NO_2-t1.DIVISION_NO_1)*100/t2.DIVISION_NO_2 as DIVISION_NO,
		(t2.Submission_no_2-t1.Submission_no_1)*100/t2.Submission_no_2 as Submission_no,
		(t2.Benchmark_Technical_Price_2-t1.Benchmark_Technical_Price_1)*100/t2.Benchmark_Technical_Price_2 as Benchmark_Technical_Price
		
	from (select count(distinct SESSION_ID) as SESSION_ID_1,
				count(distinct Account_Name) as Account_Name_1, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_1,
				count(distinct EXPIRY_DT) as EXPIRY_DT_1,
				count(distinct DIVISION_NO) as DIVISION_NO_1,
				count(distinct Submission_no) as Submission_no_1,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_1
	from bench11) t1,
		 (select count(distinct SESSION_ID) as SESSION_ID_2,
				count(distinct Account_Name) as Account_Name_2, 
				count(distinct EFFECTIVE_DT) as EFFECTIVE_DT_2,
				count(distinct EXPIRY_DT) as EXPIRY_DT_2,
				count(distinct DIVISION_NO) as DIVISION_NO_2,
				count(distinct Submission_no) as Submission_no_2,
				SUM(Benchmark_Technical_Price) as Benchmark_Technical_Price_2 
	from bench2) t2
order by 1 desc
;
quit;
Thanks,
Suryakiran
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 183 views
  • 0 likes
  • 2 in conversation