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% |
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.