topic Re: Stacking variables and calculating means in one report in SAS Programming

Stacking variables and calculating means in one report

as_methodology — Tue, 18 Sep 2018 14:27:08 GMT

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

Re: Stacking variables and calculating means in one report

Reeza — Tue, 18 Sep 2018 15:12:22 GMT

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?

Re: Stacking variables and calculating means in one report

as_methodology — Tue, 18 Sep 2018 15:23:42 GMT

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

Re: Stacking variables and calculating means in one report

Reeza — Tue, 18 Sep 2018 15:25:40 GMT

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

Re: Stacking variables and calculating means in one report

ballardw — Tue, 18 Sep 2018 15:26:35 GMT

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.

Re: Stacking variables and calculating means in one report

as_methodology — Tue, 18 Sep 2018 15:30:50 GMT

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

Re: Stacking variables and calculating means in one report

Reeza — Tue, 18 Sep 2018 15:32:58 GMT

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

Re: Stacking variables and calculating means in one report

Reeza — Tue, 18 Sep 2018 15:34:19 GMT

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

Re: Stacking variables and calculating means in one report

Tom — Tue, 18 Sep 2018 15:35:03 GMT

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

Re: Stacking variables and calculating means in one report

as_methodology — Tue, 18 Sep 2018 15:57:41 GMT

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