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;
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;
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;
@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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.