Compare two table and then find difference

subrat1 — Sat, 14 Apr 2018 11:00:12 GMT

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_ID	Account_Name	EFFECTIVE_DT	EXPIRY_DT	DIVISION_NO	Submission_no	Benchmark_Technical_Price
6138	NITRO LIFT HOLDINGS	2017-03-31	2017-12-31	26	000000000	54650
6180	THE TURBULATOR COMPANY LLC	2017-04-01	2018-02-01	26	008041409	5642
8277	Snap-On Incorporated	2018-01-01	2019-01-01	59	008767501	46795.2206
7372	Kay Polymer Trucking	2017-12-01	2018-12-01	44	008838065	31785.4632
4997	ALSINA FORMS CO., INC	2017-07-07	2018-07-07	44	012694849	28366.432
6145	WATERMARK ESTATE MANAGEMENT	2017-05-01	2018-05-01	59	019764984	9609
6137	PLURIS HOLDINGS LLC	2017-04-29	2018-04-29	26	020623607	9824

Second Table:

SESSION_ID	Account_Name	EFFECTIVE_DT	EXPIRY_DT	DIVISION_NO	Submission_no	Benchmark_Technical_Price
15926	Talajak Inc	2018-02-23	2019-02-23	26	023638834	25337.8853
17006	Stallion Express, Inc. DBA Stallion Transportation Group	2018-02-01	2019-02-01	27	024331787	2609.5322
18112	LEARNING BITS, INC.	2018-03-19	2019-03-19	44	026983808	1388.2046
13738	PAULUCA PETROLEUM INC	2018-02-03	2019-02-03	26	027570868	10752.537
13342	DESIGNER GROUP USA INC.	2018-02-01	2019-02-01	44	027878207	7958.3232
10300	SANNER OF AMERICA, INC.	2018-03-15	2019-01-01	44	028554514	1138.8927
14473	BYFOD	2018-02-15	2019-02-15	44	030697498	2671.9558
6495	Hooper Trucking Ltd	2017-10-01	2018-10-01	26	030987750	19811.1084
8339	Luera's Welding Service, Inc.	2017-11-29	2018-11-29	26	031071377	10752.537
16725	EVOCA USA LLC	2018-03-03	2019-03-03	44	031849584	7958.3232

I want my Result:

Week	SESSION_ID	Account_Name	EFFECTIVE_DT	EXPIRY_DT	DIVISION_NO	Submission_no	Benchmark_Technical_Price
Lastweek	6	6	6	6	3	6	186672.1158
Thisweek	8	8	8	8	3	8	90379.2994
Difference	2	2	2	2	0	2	-96292.8164
% Difference	25%	25%	25%	25%	0%	25%	-107%

Re: Compare two table and then find difference

SuryaKiran — Sat, 14 Apr 2018 15:13:24 GMT

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;

topic Re: Compare two table and then find difference in SAS Programming

Compare two table and then find difference

Re: Compare two table and then find difference