Hello
I am trying to compile a report that should look like this in the end:
Q1 | Q2 | Q3 | Q2 | Mean | |
Total | 459 | 452 | 416 | 347 | 418.5 |
Sex | |||||
Men | 205 | 206 | 186 | 177 | 193.5 |
Women | 254 | 246 | 230 | 170 | 225.0 |
Age | |||||
15-24 | 74 | 77 | 64 | 44 | 64.8 |
25-49 | 256 | 248 | 230 | 196 | 232.5 |
50+ | 129 | 127 | 122 | 107 | 121.3 |
On the one hand I need to stack the variables (Sex, Age) which I know how to do in proc tabulate but can't do in proc report. And on the other hand I need to calculate the mean of the four quarters, which I know how to do in proc report but not in proc tabulate.
Any ideas how to create such a report.
Many thanks in advance, Simon
Dear Reeza
Thanks for the reply. I have individual data. That is every observation has a single line:
Year | Quarter | ID | Sex | Age |
2018 | Q1 | 1234 | M | 15-24 |
2018 | Q2 | 2645 | M | 15-24 |
2018 | Q3 | 1245 | M | 25-49 |
2018 | Q4 | 8654 | F | 50+ |
2018 | Q1 | 5423 | F | 15-24 |
2018 | Q2 | 5431 | F | 15-24 |
2018 | Q3 | 98425 | M | 25-49 |
2018 | Q4 | 24343 | M | 25-49 |
2018 | Q1 | 4534 | M | 15-24 |
Quarter is the part of the year (Jan, Feb, Mar = Q1, Apr, May, Jun=Q2 and so on). Of course the second Q2 should read Q4. Mean should then be the average of the four quarters (Q1+Q2+Q3+Q4)/4.
Best, Simon
There's no values in that data set...what are the statistics/mean of?
We have the number of observations. That is in total we have 459 observations in the first quarter.
The idea is to get the average of the four quarters:
Q1 | Q2 | Q3 | Q2 | Mean | |
Total | 459 | 452 | 416 | 347 | 418.5 = (459+452+416+347)/4 |
@as_methodology wrote:
We have the number of observations. That is in total we have 459 observations in the first quarter.
The idea is to get the average of the four quarters:
Q1 Q2 Q3 Q2 Mean Total 459 452 416 347 418.5 = (459+452+416+347)/4
Ok, with this new information, my previous answer is incorrect. I'm not sure how to calculate the mean of a count within any of the summary procs so I suspect you're going to need to pre-summarize your data and use PROC REPORT to display it.
@as_methodology wrote:
We have the number of observations. That is in total we have 459 observations in the first quarter.
The idea is to get the average of the four quarters:
Q1 Q2 Q3 Q2 Mean Total 459 452 416 347 418.5 = (459+452+416+347)/4
If you always have 4 quarters then mean is just grand total divided by 4.
That is correct. However, I do not know how to calculate this in SAS.
Sometimes it is easiest to pre-summarize the data values and then use Proc Report/Tabulate/Print just to display the result.
Tabulate in general does not do calculation using the results of other statistics (limited ability with PCTSUM type statistics).
Example starting data is always helpful.
I think this is exactly what you want, using PROC TABULATE. You can map it to your data to see how it should work, but since you didn't provide sample data I worked off sashelp.heart.
Part of the key here is the ALL keyword, that will do the summaries.
proc tabulate data=sashelp.heart;
class status sex smoking_status;
var systolic;
table all (status) (sex), (smoking_status all)*systolic*Mean ;
run;
@as_methodology wrote:
Hello
I am trying to compile a report that should look like this in the end:
Q1 Q2 Q3 Q2 Mean Total 459 452 416 347 418.5 Sex Men 205 206 186 177 193.5 Women 254 246 230 170 225.0 Age 15-24 74 77 64 44 64.8 25-49 256 248 230 196 232.5 50+ 129 127 122 107 121.3
On the one hand I need to stack the variables (Sex, Age) which I know how to do in proc tabulate but can't do in proc report. And on the other hand I need to calculate the mean of the four quarters, which I know how to do in proc report but not in proc tabulate.
Any ideas how to create such a report.
Many thanks in advance, Simon
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.