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;