Stacking variables and calculating means in one report

[as_methodology]

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

[Reeza]

What does your input data look like? That determines how easily this can be built. Is Q1, Q2, Q3 variables or are those quartiles? And why are there two Q2s?

[as_methodology]

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

[Reeza]

There's no values in that data set...what are the statistics/mean of?

[as_methodology]

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

[Reeza]

---

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

[Tom]

---

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

[as_methodology]

That is correct. However, I do not know how to calculate this in SAS.

[ballardw]

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.

[Reeza]

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

---