topic Re: Need help with sum and mean tabular report in New SAS User

Need help with sum and mean tabular report

Cbob03 — Mon, 10 Feb 2020 03:44:09 GMT

Hello,

I am trying to produce a cross tabulation report with sum and mean values and I want my report to be generated in excel.

Here is my data:

Date	Week_day	Sample_typ	Count	avg_temp
03Feb2020	Monday	TYP3	2234	134.15
03Feb2020	Monday	TYP1	11233	162.08
03Feb2020	Monday	TYP2	109	14.08
05Feb2020	Wednesday	TYP4	1245	36.19
05Feb2020	Wednesday	TYP2	1056	171.13
05Feb2020	Wednesday	TYP1	18	32.00
06Feb2020	Thursday	TYP4	1	12.00
06Feb2020	Thursday	TYP2	334	77.18
07Feb2020	Friday	TYP1	1790	134.61
07Feb2020	Friday	TYP4	301	71.61
07Feb2020	Friday	TYP3	4509	10.74
07Feb2020	Friday	TYP2	889	189.39
08Feb2020	Saturday	TYP1	190	34.56

My desired output:

I tried the below code but I am unable to insert the “Avg Temp”. Can someone please show me how to create the report in the above format? Thanks.

proc tabulate data=sample f=comma8.0 s=[foreground=black just=c];

class date date Week_day Sample_typ;

var ct avg_temp;

table date = 'Dt' * Week_day='Week Day' all={label='Total' s=[just=c]},

((Sample_typ = {label ="Volume by Sample Type"

s=[just=c]} ) * (ct='' * (sum=''*f=comma8.0)) ) /

misstext='-';

run;

Re: Need help with sum and mean tabular report

Ksharp — Mon, 10 Feb 2020 04:39:51 GMT

data have;
input Date : $10.
Week_day $	
Sample_typ $
Ct
avg_temp;
cards;
03Feb2020
Monday
TYP3
2234
134.15
03Feb2020
Monday
TYP1
11233
162.08
03Feb2020
Monday
TYP2
109
14.08
05Feb2020
Wednesday
TYP4
1245
36.19
05Feb2020
Wednesday
TYP2
1056
171.13
05Feb2020
Wednesday
TYP1
18
32.00
06Feb2020
Thursday
TYP4
1
12.00
06Feb2020
Thursday
TYP2
334
77.18
07Feb2020
Friday
TYP1
1790
134.61
07Feb2020
Friday
TYP4
301
71.61
07Feb2020
Friday
TYP3
4509
10.74
07Feb2020
Friday
TYP2
889
189.39
08Feb2020
Saturday
TYP1
190
34.56
;

proc tabulate data=have;
class date Week_day Sample_typ;
var ct avg_temp;
table date = 'Dt' * Week_day='Week Day' all={label='Total' s=[just=c]},
((Sample_typ = {label ="Volume by Sample Type"
s=[just=c]} ) * (ct='#'*f=comma8.0 avg_temp='Avt Temp'*f=comma8.0) ) /
misstext='-';
keylabel sum=' ';
run;

Re: Need help with sum and mean tabular report

unison — Mon, 10 Feb 2020 05:41:24 GMT

@Cbob03 , @Ksharp offered a solid solution above.

I've adjusted it slightly since it looks like the avg_temp for the total row needs to be a weighted average.

Please credit @Ksharp, though.

proc tabulate data=have;
class date Week_day Sample_typ;
var avg_temp / weight=ct;
table date = 'Dt' * Week_day='Week Day' all={label='Total' s=[just=c]},
((Sample_typ = {label ="Volume by Sample Type"
s=[just=c]} ) * (ct='#'*f=comma8.0 avg_temp*mean=' '*f=comma8.0) ) /
misstext='-';
keylabel sum=' ';
run;

Re: Need help with sum and mean tabular report

Cbob03 — Tue, 11 Feb 2020 01:29:01 GMT

Thank you Unison!!