cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Whitlea
Obsidian | Level 7 Whitlea
Obsidian | Level 7
Go to Solution

Adding Distinct Totals to Proc Tabulate

Posted 05-29-2025 06:21 PM (1029 views)

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!

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User Ksharp
Super User

Re: Adding Distinct Totals to Proc Tabulate

Posted 06-01-2025 08:16 AM (858 views)  |   In reply to Whitlea

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;

Ksharp_0-1748780187665.png

 

View solution in original post

Reply
4 REPLIES 4
Ksharp
Super User Ksharp
Super User

Re: Adding Distinct Totals to Proc Tabulate

Posted 05-29-2025 11:33 PM (971 views)  |   In reply to Whitlea

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;

Ksharp_0-1748576940372.png

 

 

Reply
Whitlea
Obsidian | Level 7 Whitlea
Obsidian | Level 7

Re: Adding Distinct Totals to Proc Tabulate

Posted 05-30-2025 05:26 PM (916 views)  |   In reply to Ksharp
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?
0 Likes
Reply
Ksharp
Super User Ksharp
Super User

Re: Adding Distinct Totals to Proc Tabulate

Posted 06-01-2025 07:40 AM (863 views)  |   In reply to Whitlea

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_0-1748778039576.png

 

0 Likes
Reply
Ksharp
Super User Ksharp
Super User

Re: Adding Distinct Totals to Proc Tabulate

Posted 06-01-2025 08:16 AM (859 views)  |   In reply to Whitlea

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;

Ksharp_0-1748780187665.png

 

Reply

sasinnovate.png



April 27 – 30 | Gaylord Texan | Grapevine, Texas

Registration is open

Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!

Register now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • ‎05-29-2025 06:21 PM
  • 1030 views
  • 2 likes
  • 2 in conversation