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;
... View more
