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