Adding Distinct Totals to Proc Tabulate

[Whitlea]

I have 2 things I need help with.

1. My proc tabulate is generating 2 of the same table and I’m not sure why. Can anyone tell me why it’s doing this?
2. I need help with creating a table just like the one this proc tabulate generates except, for the total columns and rows I want total distinct count for groups A,B,and C instead of the sum.  I want to keep the sum as the totals for the $ group. Maybe it would be better to use proc sql for this?

   Data Sample;
   input 
   
   IDA$	IDB$	Month$	Group_ID$	Cost	Yr$	IDC$	Type$;
   datalines;
   428	408	Mar	2	67	2020	1	A
   196	176	Feb	2	27	2021	2	A
   423	403	Oct	2	160	2023	3	A
   191	171	Nov	2	53	2021	4	A
   186	166	Sep	1	186	2020	5	A
   186	166	Feb	1	226	2020	6	A
   421	401	Jan	1	160	2020	6	A
   536	516	Nov	1	53	2021	6	A
   197	177	Jan	1	80	2021	6	A
   184	164	May	2	173	2020	6	A
   190	170	July	1	160	2020	11	A
   183	163	Aug	1	293	2020	12	A
   185	165	Feb	1	306	2020	13	A
   193	173	Apr	1	280	2020	14	A
   187	167	Nov	1	160	2020	15	A
   189	169	Aug	1	147	2021	16	A
   184	164	Dec	1	320	2020	17	A
   184	164	May	2	133	2020	18	A
   184	164	June	2	293	2020	18A
   187	167	Nov	1	80	2021	18	A
   191	171	Mar	1	160	2020	18	A
   535	515	Apr	1	160	2020	18	A
   179	159	Feb	1	80	2020	23	A
   187	167	May	2	173	2020	24	A
   676	656	July	1	67	2021	25	A
   187	167	Feb	1	160	2021	26	A
   196	176	Mar	1	133	2020	27	A
   197	177	July	1	160	2021	30	A
   425	405	Dec	1	173	2021	30	A
   185	165	Jan	1	120	2020	30	A
   ;;;
   run;
   
   /*Distinct Counts*/
   Proc sql;
   create table Counts as
   select Type,Yr,Month,Group_ID,count(distinct IDB)as B format=comma10.,count(distinct IDA)as A format=comma10.,count(distinct IDC)as C format=comma10.
   from Sample
   Group by Type,Yr,Month,Group_ID;
   quit;
   
   /*Cost*/
   Proc sql;
   create table Cost as
   select Type,Yr,Month,Group_ID, sum(Cost)as Cost format=dollar12.
   from Sample
   where cost>0
   Group by Type,Yr,Month,Group_ID,Group_ID;
   quit;
   
   /*Distinct Totals*/
   Proc sql;
   create table Distinct_Totals as
   select Type,Yr,Month,count(distinct IDB)as B_TotalR format=comma10.,count(distinct IDA)as A_TotalR format=comma10.,count(distinct IDC)as C_TotalR format=comma10.
   from Sample
   Group by Type,Yr,Month
   outer union corr
   select Type,Yr,Group_ID,count(distinct IDB)as B_TotalC format=comma10.,count(distinct IDA)as A_TotalC format=comma10.,count(distinct IDC)as C_TotalC format=comma10.
   from Sample
   Group by Type,Yr,Group_ID;
   quit;
   
   /*Cost and Distinct Counts*/
   data All_Data;
   merge Cost Counts;
   by Type Yr Month Group_ID;
   run;
   
   proc tabulate data= All_Data format=comma6.;
   class Type Yr Month Group_ID;
   var B A C Cost;
   table Type sum=' ',(B A C Cost="$"*format=dollar12. )*(Group_ID=' ' all=[label='Total' style=[background=cxF0F0F0]]*[style=[background=cxF0F0F0]]),
   Yr=''*(Month='' all=[label='Total' style=[background=cxF0F0F0]]*[style=[background=cxF0F0F0]]) /misstext=' ';
   run;

   Thanks!

[Ksharp]

You want this ?

Data Sample;
input IDA$	IDB$	Month$	Group_ID$	Cost	Yr$	IDC$	Type$;
datalines;
428	408	Mar	2	67	2020	1	A
196	176	Feb	2	27	2021	2	A
423	403	Oct	2	160	2023	3	A
191	171	Nov	2	53	2021	4	A
186	166	Sep	1	186	2020	5	A
186	166	Feb	1	226	2020	6	A
421	401	Jan	1	160	2020	6	A
536	516	Nov	1	53	2021	6	A
197	177	Jan	1	80	2021	6	A
184	164	May	2	173	2020	6	A
190	170	July	1	160	2020	11	A
183	163	Aug	1	293	2020	12	A
185	165	Feb	1	306	2020	13	A
193	173	Apr	1	280	2020	14	A
187	167	Nov	1	160	2020	15	A
189	169	Aug	1	147	2021	16	A
184	164	Dec	1	320	2020	17	A
184	164	May	2	133	2020	18	A
184	164	June	2	293	2020	18  A
187	167	Nov	1	80	2021	18	A
191	171	Mar	1	160	2020	18	A
535	515	Apr	1	160	2020	18	A
179	159	Feb	1	80	2020	23	A
187	167	May	2	173	2020	24	A
676	656	July	1	67	2021	25	A
187	167	Feb	1	160	2021	26	A
196	176	Mar	1	133	2020	27	A
197	177	July	1	160	2021	30	A
425	405	Dec	1	173	2021	30	A
185	165	Jan	1	120	2020	30	A
;;;
run;

/*Distinct Counts*/
Proc sql;
create table report as
select Type,Yr,Month,Group_ID,' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,Group_ID,' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,Group_ID,' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Month,Group_ID

/*total*/
union
select Type,Yr,Month,'total',' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr,Month
union
select Type,Yr,Month,'total',' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Month
union
select Type,Yr,Month,'total',' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Month

union
select Type,Yr,'total',Group_ID,' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr,Group_ID
union
select Type,Yr,'total',Group_ID,' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Group_ID
union
select Type,Yr,'total',Group_ID,' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Group_ID

union
select Type,Yr,'total','total',' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr
union
select Type,Yr,'total','total',' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr
union
select Type,Yr,'total','total',' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr



/*cost*/
union
select Type,Yr,Month,Group_ID,'$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,'total','$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr,Month
union
select Type,Yr,'total',Group_ID,'$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr,Group_ID
union
select Type,Yr,'total','total','$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr

;


quit;

proc report data=report nowd spanrows;
column type name group_id value,yr,month;
define type/group;
define name/group;
define group_id/group;
define yr/across '' nozero;
define month/across '' nozero;
define value/group '' nozero;
run;

[Whitlea]

Yes, this is what I needed. Thank you! Is there a way to make the value cells gray if the Month or Group_ID=Total?

[Ksharp]

If this report is generated under rtf file :

Data Sample;
input IDA$	IDB$	Month$	Group_ID$	Cost	Yr$	IDC$	Type$;
datalines;
428	408	Mar	2	67	2020	1	A
196	176	Feb	2	27	2021	2	A
423	403	Oct	2	160	2023	3	A
191	171	Nov	2	53	2021	4	A
186	166	Sep	1	186	2020	5	A
186	166	Feb	1	226	2020	6	A
421	401	Jan	1	160	2020	6	A
536	516	Nov	1	53	2021	6	A
197	177	Jan	1	80	2021	6	A
184	164	May	2	173	2020	6	A
190	170	July	1	160	2020	11	A
183	163	Aug	1	293	2020	12	A
185	165	Feb	1	306	2020	13	A
193	173	Apr	1	280	2020	14	A
187	167	Nov	1	160	2020	15	A
189	169	Aug	1	147	2021	16	A
184	164	Dec	1	320	2020	17	A
184	164	May	2	133	2020	18	A
184	164	June	2	293	2020	18  A
187	167	Nov	1	80	2021	18	A
191	171	Mar	1	160	2020	18	A
535	515	Apr	1	160	2020	18	A
179	159	Feb	1	80	2020	23	A
187	167	May	2	173	2020	24	A
676	656	July	1	67	2021	25	A
187	167	Feb	1	160	2021	26	A
196	176	Mar	1	133	2020	27	A
197	177	July	1	160	2021	30	A
425	405	Dec	1	173	2021	30	A
185	165	Jan	1	120	2020	30	A
;;;
run;

/*Distinct Counts*/
Proc sql;
create table report as
select Type,Yr,Month,Group_ID,' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=80
from Sample
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,Group_ID,' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,Group_ID,' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Month,Group_ID

/*total*/
union
select Type,Yr,Month,'total',' B' as name length=20,cats('(*ESC*)S={background=greydd}',put(count(distinct IDB),comma10. -l)) as value length=40
from Sample
Group by Type,Yr,Month
union
select Type,Yr,Month,'total',' A',cats('(*ESC*)S={background=greydd}',put(count(distinct IDA),comma10. -l))  
from Sample
Group by Type,Yr,Month
union
select Type,Yr,Month,'total',' C',cats('(*ESC*)S={background=greydd}',put(count(distinct IDC),comma10. -l)) 
from Sample
Group by Type,Yr,Month

union
select Type,Yr,'total',Group_ID,' B' as name length=20,cats('(*ESC*)S={background=greydd}',put(count(distinct IDB),comma10. -l)) as value length=40
from Sample
Group by Type,Yr,Group_ID
union
select Type,Yr,'total',Group_ID,' A',cats('(*ESC*)S={background=greydd}',put(count(distinct IDA),comma10. -l))  
from Sample
Group by Type,Yr,Group_ID
union
select Type,Yr,'total',Group_ID,' C',cats('(*ESC*)S={background=greydd}',put(count(distinct IDC),comma10. -l)) 
from Sample
Group by Type,Yr,Group_ID

union
select Type,Yr,'total','total',' B' as name length=20,cats('(*ESC*)S={background=greydd}',put(count(distinct IDB),comma10. -l)) as value length=40
from Sample
Group by Type,Yr
union
select Type,Yr,'total','total',' A',cats('(*ESC*)S={background=greydd}',put(count(distinct IDA),comma10. -l))  
from Sample
Group by Type,Yr
union
select Type,Yr,'total','total',' C',cats('(*ESC*)S={background=greydd}',put(count(distinct IDC),comma10. -l)) 
from Sample
Group by Type,Yr



/*cost*/
union
select Type,Yr,Month,Group_ID,'$', put(sum(Cost),dollar12. -l)
from Sample
where cost>0
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,'total','$', cats('(*ESC*)S={background=greydd}',put(sum(Cost),dollar12. -l)) 
from Sample
where cost>0
Group by Type,Yr,Month
union
select Type,Yr,'total',Group_ID,'$', cats('(*ESC*)S={background=greydd}',put(sum(Cost),dollar12. -l)) 
from Sample
where cost>0
Group by Type,Yr,Group_ID
union
select Type,Yr,'total','total','$', cats('(*ESC*)S={background=greydd}',put(sum(Cost),dollar12. -l)) 
from Sample
where cost>0
Group by Type,Yr

;


quit;

option orientation=landscape;
ods rtf file='c:\temp\want.rtf' style=journal;
proc report data=report nowd spanrows;
column type name group_id value,yr,month;
define type/group;
define name/group;
define group_id/group;
define yr/across '' nozero;
define month/across '' nozero;
define value/group '' nozero;
compute group_id;
if group_id='total' then call define(_row_,'style','style={background=greydd}');
endcomp;
run;
ods rtf close;

[Ksharp · Accepted solution]

Or you could identify the column name of TOTAL by option OUT= .

Data Sample;
input IDA$	IDB$	Month$	Group_ID$	Cost	Yr$	IDC$	Type$;
datalines;
428	408	Mar	2	67	2020	1	A
196	176	Feb	2	27	2021	2	A
423	403	Oct	2	160	2023	3	A
191	171	Nov	2	53	2021	4	A
186	166	Sep	1	186	2020	5	A
186	166	Feb	1	226	2020	6	A
421	401	Jan	1	160	2020	6	A
536	516	Nov	1	53	2021	6	A
197	177	Jan	1	80	2021	6	A
184	164	May	2	173	2020	6	A
190	170	July	1	160	2020	11	A
183	163	Aug	1	293	2020	12	A
185	165	Feb	1	306	2020	13	A
193	173	Apr	1	280	2020	14	A
187	167	Nov	1	160	2020	15	A
189	169	Aug	1	147	2021	16	A
184	164	Dec	1	320	2020	17	A
184	164	May	2	133	2020	18	A
184	164	June	2	293	2020	18  A
187	167	Nov	1	80	2021	18	A
191	171	Mar	1	160	2020	18	A
535	515	Apr	1	160	2020	18	A
179	159	Feb	1	80	2020	23	A
187	167	May	2	173	2020	24	A
676	656	July	1	67	2021	25	A
187	167	Feb	1	160	2021	26	A
196	176	Mar	1	133	2020	27	A
197	177	July	1	160	2021	30	A
425	405	Dec	1	173	2021	30	A
185	165	Jan	1	120	2020	30	A
;;;
run;

/*Distinct Counts*/
Proc sql;
create table report as
select Type,Yr,Month,Group_ID,' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,Group_ID,' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,Group_ID,' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Month,Group_ID

/*total*/
union
select Type,Yr,Month,'total',' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr,Month
union
select Type,Yr,Month,'total',' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Month
union
select Type,Yr,Month,'total',' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Month

union
select Type,Yr,'total',Group_ID,' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr,Group_ID
union
select Type,Yr,'total',Group_ID,' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr,Group_ID
union
select Type,Yr,'total',Group_ID,' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr,Group_ID

union
select Type,Yr,'total','total',' B' as name length=20,put(count(distinct IDB),comma10. -l) as value length=40
from Sample
Group by Type,Yr
union
select Type,Yr,'total','total',' A',put(count(distinct IDA),comma10. -l)  
from Sample
Group by Type,Yr
union
select Type,Yr,'total','total',' C',put(count(distinct IDC),comma10. -l) 
from Sample
Group by Type,Yr



/*cost*/
union
select Type,Yr,Month,Group_ID,'$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr,Month,Group_ID
union
select Type,Yr,Month,'total','$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr,Month
union
select Type,Yr,'total',Group_ID,'$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr,Group_ID
union
select Type,Yr,'total','total','$', put(sum(Cost),dollar12. -l) 
from Sample
where cost>0
Group by Type,Yr

;


quit;


proc report data=report nowd spanrows out=x;
column type name group_id value,yr,month;
define type/group;
define name/group;
define group_id/group;
define yr/across '' nozero;
define month/across '' nozero;
define value/group '' nozero;
compute group_id;
if group_id='total' then call define(_row_,'style','style={background=greydd}');
endcomp;
compute month;
call define('_c16_','style','style={background=greydd}');
call define('_c29_','style','style={background=greydd}');
call define('_c42_','style','style={background=greydd}');
endcomp;
run;