topic Re: Adding Distinct Totals to Proc Tabulate in SAS Programming

Adding Distinct Totals to Proc Tabulate

Whitlea — Thu, 29 May 2025 22:21:58 GMT

I have 2 things I need help with.

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?

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!

Re: Adding Distinct Totals to Proc Tabulate

Ksharp — Fri, 30 May 2025 03:49:07 GMT

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;

Re: Adding Distinct Totals to Proc Tabulate

Whitlea — Fri, 30 May 2025 21:26:54 GMT

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?

Re: Adding Distinct Totals to Proc Tabulate

Ksharp — Sun, 01 Jun 2025 11:40:52 GMT

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;

Re: Adding Distinct Totals to Proc Tabulate

Ksharp — Sun, 01 Jun 2025 12:16:35 GMT

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;