BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Cbob03
Fluorite | Level 6

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:

 

Output.PNG

 

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
Ksharp
Super User
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;
unison
Lapis Lazuli | Level 10

@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;

 

-unison
Cbob03
Fluorite | Level 6
Thank you Unison!!

SAS Innovate 2025: Register Now

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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 546 views
  • 1 like
  • 3 in conversation