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;
... View more