Hello I created a program that need to create a requested summary table . I would like to ask If anyone can recommend a more useful way (less code rows) to create it. Please find the code . It is using Macro because in the future I might run it again with different dates. The target is to have a flexible code. tbl1806 and tbl1712 are input tables.(tables that we get from external source) thanks Joe %let vector=1806+1712;
%let CurMon=%scan(&vector.,1,+);
%let RefMon=%scan(&vector.,2,+);
%put &CurMon.;
%put &RefMon.;
Data tbl1806;
input ID team $ Y ;
cards;
1 817 10
2 817 20
3 818 30
4 828 40
5 818 50
6 890 60
7 890 70
8 890 80
9 890 90
10 890 100
;
Run;
Data tbl1712;
input ID team $ Y;
cards;
1 817 15
2 817 25
3 818 35
4 828 45
5 818 55
6 890 65
7 818 70
11 818 110
12 818 120
;
Run;
%macro mmacro1;
%DO i=1 %TO 2;
%let mon=%scan(&vector.,&i.,+);
PROC SQL;
create table tbl_817_&mon. as
select 1 as w,
'817' as type,
sum(case when team='817' then 1 else 0 end )as No_Customers&mon.,
sum(case when team='817' then Y else 0 end ) as Y&mon.
from tbl&mon.
where team IN('817')
;
quit;
PROC SQL;
create table tbl_818_&mon. as
select 1 as w,
'818' as type,
sum(case when team='818' then 1 else 0 end )as No_Customers&mon.,
sum(case when team='818' then Y else 0 end ) as Y&mon.
from tbl&mon.
where team IN('818')
;
quit;
PROC SQL;
create table tbl_828_&mon. as
select 1 as w,
'828' as type,
sum(case when team='828' then 1 else 0 end )as No_Customers&mon.,
sum(case when team='828' then Y else 0 end ) as Y&mon.
from tbl&mon.
where team IN('828')
;
quit;
PROC SQL;
create table tbl_No_817_818_828_&mon. as
select 1 as w,
'No_817_818_828' as type,
count(*) as No_Customers&mon.,
sum(Y) as Y&mon.
from tbl&mon.
where team NOT IN('817','818','828')
;
quit;
PROC SQL;
create table tbl_All_&mon. as
select 1 as w,
'ALL' as type,
count(*) as No_Customers&mon.,
sum(Y) as Y&mon.
from tbl&mon.
;
quit;
%end;
%mend;
%mmacro1;
Data tbl_817;
Merge tbl_817_:;
By w;
run;
Data tbl_818;
Merge tbl_818_:;
By w;
run;
Data tbl_828;
Merge tbl_828_:;
By w;
run;
Data tbl_No817_818_828(drop=w);
Merge tbl_No_817_818_828_:;
By w;
Run;
Data tbl_All;
Merge tbl_All_:;
By w;
run;
DATA tbl_a;
SET tbl_817 tbl_818 tbl_828;
run;
PROC SQL;
create table tbl_b as
select 'TOTAL 817_818_828' as type,
sum(No_Customers&RefMon.) as No_Customers&RefMon.,
sum(Y&RefMon.) as Y&RefMon.,
sum(No_Customers&CurMON.) as No_Customers&CurMON.,
sum(Y&CurMON.) as Y&CurMON.
from tbl_a
;
QUIT;
DATA tbl_c;
length Type $50;
SET tbl_a(drop=w) tbl_b;
run;
DATA tbl_d;
SET tbl_c tbl_No817_818_828 tbl_All;
w=1;
run;
Data tbl_e;
Merge tbl_d(in=a) tbl_All(in=b drop=type rename=(No_Customers&RefMon.=TOTAL_Customers&RefMon.
Y&RefMon.=TOTAL_Y&RefMon.
No_Customers&CurMON.=TOTAL_Customers&CurMON.
Y&CurMON.=TOTAL_Y&CurMON.));
by w;
run;
Data tbl_f(drop=W TOTAL_Customers&RefMon. TOTAL_Customers&CurMON. TOTAL_Y&RefMon. TOTAL_Y&CurMON.);
SET tbl_e;
PCT_Customers&RefMon.=No_Customers&RefMon./TOTAL_Customers&RefMon.;
PCT_Customers&CurMON.=No_Customers&CurMON./TOTAL_Customers&CurMON.;
PCT_Y&RefMon.=Y&RefMon./TOTAL_Y&RefMon.;
PCT_Y&CurMON.=Y&CurMON./TOTAL_Y&CurMON.;
Diff_Customers=No_Customers&CurMON.-No_Customers&RefMon.;
Diff_PCT_Customers=PCT_Customers&CurMON.-PCT_Customers&RefMon.;
Diff_Y=Y&CurMON.-Y&RefMon.;
Diff_PCT_Y=PCT_Y&CurMON.-PCT_Y&RefMon.;
Format PCT_Customers&CurMON. PCT_Customers&RefMon. Diff_PCT_Customers
PCT_Y&RefMon. PCT_Y&CurMON. Diff_PCT_Y percent9.2;
Run;
proc report data=tbl_f nowd headline headskip
style(report)=[rules=all cellspacing=0 bordercolor=black]
style(header)=[background=lightgrey foreground=black fontsize=4]
style(column)=[ fontsize=3] ;
column Type
( "&CurMON." No_Customers&CurMON. Y&CurMON. PCT_Customers&CurMON. PCT_Y&CurMON.)
( "&RefMon." No_Customers&RefMon. Y&RefMon. PCT_Customers&RefMon. PCT_Y&RefMon.)
('Customers' Diff_Customers Diff_PCT_Customers)
('Revenue' Diff_Y Diff_PCT_Y);
define type/display style(column)={width=1in} ;
define No_Customers&CurMON./display style(column)={width=1in};
define Y&CurMON./display style(column)={width=1in} ;
define PCT_Customers&CurMON./display style(column)={width=1in} ;
define PCT_Y&CurMON./display style(column)={width=1in} ;
define No_Customers&RefMon./display style(column)={width=1in} ;
define Y&RefMon./display style(column)={width=1in} ;
define PCT_Customers&RefMon./display style(column)={width=1in} ;
define PCT_Y&RefMon./display style(column)={width=1in} ;
define Diff_Customers/display style(column)={width=1in} ;
define Diff_PCT_Customers/display style(column)={width=1in} ;
define Diff_Y/display style(column)={width=1in} ;
define Diff_PCT_Y/display style(column)={width=1in} ;
Run;
... View more
