Please find the code that creates the desired report.
As you can see it is a very long code and the target is to find a better code(short).
Difference is value in 1809 minus value in 1712.
Number of customers is number of rows.(Each customer has 1 row in each month table)
Data Tbl_1809;
input ID Team Y month;
cards;
1 817 10 1809
2 817 20 1809
3 818 30 1809
4 828 40 1809
5 828 50 1809
6 828 60 1809
7 811 70 1809
8 810 80 1809
9 810 90 1809
10 809 100 1809
;
run;
Data Tbl_1712;
input ID Team Y month;
cards;
2 817 25 1712
3 818 35 1712
4 828 45 1712
5 828 56 1712
6 828 65 1712
7 811 75 1712
11 817 85 1712
12 817 95 1712
13 818 100 1712
;
run;
PROC SQL;
create table P_817_1809 as
select '817' as type,
sum(case when Team=817 then 1 else 0 end )as No_Customers1809,
sum(case when Team=817 then Y else 0 end ) as Y1809
from Tbl_1809
where Team=817
;
QUIT;
PROC SQL;
create table P_817_1712 as
select '817' as type,
sum(case when Team=817 then 1 else 0 end )as No_Customers1712,
sum(case when Team=817 then Y else 0 end ) as Y1712
from Tbl_1712
where Team=817
;
QUIT;
PROC SQL;
create table P_818_1809 as
select '818' as type,
sum(case when Team=818 then 1 else 0 end )as No_Customers1809,
sum(case when Team=818 then Y else 0 end ) as Y1809
from Tbl_1809
where Team=818
;
QUIT;
PROC SQL;
create table P_818_1712 as
select '818' as type,
sum(case when Team=818 then 1 else 0 end )as No_Customers1712,
sum(case when Team=818 then Y else 0 end ) as Y1712
from Tbl_1712
where Team=818
;
QUIT;
PROC SQL;
create table P_828_1809 as
select '828' as type,
sum(case when Team=828 then 1 else 0 end )as No_Customers1809,
sum(case when Team=828 then Y else 0 end ) as Y1809
from Tbl_1809
where Team=828
;
QUIT;
PROC SQL;
create table P_828_1712 as
select '828' as type,
sum(case when Team=828 then 1 else 0 end )as No_Customers1712,
sum(case when Team=828 then Y else 0 end ) as Y1712
from Tbl_1712
where Team=828
;
QUIT;
PROC SQL;
create table P_817_818_828_1712 as
select '817_818_828' as type,
sum(case when Team in(817,818,828) then 1 else 0 end )as No_Customers1712,
sum(case when Team in(817,818,828) then Y else 0 end ) as Y1712
from Tbl_1712
where Team in(817,818,828)
;
QUIT;
PROC SQL;
create table P_817_818_828_1809 as
select '817_818_828' as type,
sum(case when Team in(817,818,828) then 1 else 0 end )as No_Customers1809,
sum(case when Team in(817,818,828) then Y else 0 end ) as Y1809
from Tbl_1809
where Team in(817,818,828)
;
QUIT;
PROC SQL;
create table P_All_1809 as
select 'All' as type,
count(*) as No_Customers1809,
sum(Y) as Y1809
from Tbl_1809
;
QUIT;
PROC SQL;
create table P_All_1712 as
select 'All' as type,
count(*) as No_Customers1712,
sum(Y) as Y1712
from Tbl_1809
;
QUIT;
Data P_817;
length Type $20;
Merge P_817:;
run;
Data P_818;
length Type $20;
Merge P_818:;
run;
Data P_828;
length Type $20;
Merge P_828:;
run;
DATA P_817_818_828;
length Type $20;
Merge P_817_818_828_:;
run;
Data P_All;
Merge P_All:;
Run;
Data output;
SET P_817 P_818 P_828 P_817_818_828 P_All;
D_Y=Y1809-Y1712;
D_Customers=No_Customers1809-No_Customers1712;
PCT_Change_Y=(Y1809-Y1712)/Y1712;
PCT_Change_Customers=(No_Customers1809-No_Customers1712)/No_Customers1712;
format PCT_Change_Y PCT_Change_Customers percent9.2;
Run;
... View more
