Better way to create summary table

[Ronein]

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;


















 

[ballardw]

It would really help answer this question if you provided an example of what the final table layout should be and some idea of the actual rules involved.

Why the use of the Vector macro variable? What do the actual elements of that "vector" mean in terms of the data? If mon values of 1806 are supposed to be some how related to Jun 2018 don't obfuscate such elements. If they are the suffixes in data set names you may need to expand on where those names come from and exactly which names you may need to use again.

There are many examples on this forum of using files or datasets with names containing various date bits.

[Ronein]

Hello

I think that you are missing the point.

You just need to run my code and see the output table ,which was created by Proc Report.

Then you should use 2 input tables in order to create same output.

Then we can see if your code is more efficient and is written in a better way.

 Thanks

Joe

[Ronein]

Please run my code and see the output of proc report.
This is output table.
What is not clear?

[ballardw]

---

@Ronein wrote:
Please run my code and see the output of proc report.
This is output table.
What is not clear?

---

I  asked for a description because poor code can seriously obfuscate what is desired. For instance why not have a variable that contains the value of MON and use that as a SINGLE variable that nests the analysis variables?

Conceptually: mon,(n_of_something sum_of_something)

also your "groups" of the 817 818 828 are no indicator of what to do when one of them is missing or what is done with a different value such as 838 appears. So what are 1) all of the possible values of TYPE that will ever occur and 2) which combinations are to be reported when they occur.

Likely a multilabel format for type and Proc summary or means would be a much more flexible, especially in terms of "lines of code" to summarize the data then creating a dozen or so data sets.

Example that will require some cleaning up to get column labels and add the other comparison variables:

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;

data combined;
   set tbl1806 tbl1712 indsname=ds;
   length mon $ 5;
   mon = substr(scan(ds,2,'.'),4);
run;

proc format library=work;
value $type (multilabel notsorted)
'817' = '817'
'818' = '818'
'828' = '828'
'817','818','828' = '817_818_828'
' '='All'
other = 'not 817_818_828'
;
run;


Proc report data=combined nowd;
   columns team mon,(n pctn sum pctsum),y diff_cust ;
   define team/ group mlf format=$type. preloadfmt order=data;
   define mon/across order=data;
   rbreak after/ summarize;
   define diff_cust/ computed;
   compute diff_cust;
      diff_cust = _c2_ - _c6_;
   endcomp ;
run;

[Ronein · Accepted solution]

It is perfect and a very nice code and I would like to learn it.Thank you so much.

I saw that there are 3 more columns that are requested and are not in your report.

How can I add them please to your code?

Please see the 3 more columns that are requested:

Diff_Y=_C4_-_C8_;
Diff_PCT_cust=_C3_-_C7_;
Diff_PCT_Y=_C5_-_C9_;

[Reeza]

See if you can find a way to avoid hard coding your values. 

817_818_828