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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.