topic Proc Report: Add Column for average of each row in SAS Programming

Proc Report: Add Column for average of each row

SAS93 — Thu, 09 Dec 2021 16:32:49 GMT

I a table:

	Year
	2016	2017	2018	2019	2020
Region	Row_Percent	Row_Percent	Row_Percent	Row_Percent	Row_Percent
East	31.65	30.15	53.66	58.37	50.8
Middle	44.39	46.45	36.83	31.2	40.45
West	23.95	23.39	9.51	10.44	8.75

It's produced using this code:

Proc report data=year3_region nowd;
	Column	region YY4_DOB,Row_Percent dummyvar;
	Define	region / group;
	Define yy4_DOB / across;
	Define Row_Percent / display ;
	** Dummy Var to help with formatting;
	Define dummyvar / computed noprint ;
		compute dummyvar;	dummyvar = 1;	endcomp;
Run;

The Row_percent comes from a previous crosstab output I retrieved.

If possible, Iwant to add a new column at the end that represents the average of that row_percent across the 5-year period for each region. I've tried a few different examples I've found online, but none of them have worked. What's the best way to do this?

Re: Proc Report: Add Column for average of each row

ballardw — Thu, 09 Dec 2021 17:08:39 GMT

If the denominator used in calculating the percent of each column is not exactly the same, or at least pretty close to identical, then the average of percentage is misleading at best and an out right lie for meaning at worst.

So, how similar are the denominators involved?

@SAS93 wrote:

I a table:
Year

2016 2017 2018 2019 2020

Region Row_Percent Row_Percent Row_Percent Row_Percent Row_Percent

East 31.65 30.15 53.66 58.37 50.8

Middle 44.39 46.45 36.83 31.2 40.45

West 23.95 23.39 9.51 10.44 8.75

It's produced using this code:
Proc report data=year3_region nowd;
	Column	region YY4_DOB,Row_Percent dummyvar;
	Define	region / group;
	Define yy4_DOB / across;
	Define Row_Percent / display ;
	** Dummy Var to help with formatting;
	Define dummyvar / computed noprint ;
		compute dummyvar;	dummyvar = 1;	endcomp;
Run;
The Row_percent comes from a previous crosstab output I retrieved.

If possible, Iwant to add a new column at the end that represents the average of that row_percent across the 5-year period for each region. I've tried a few different examples I've found online, but none of them have worked. What's the best way to do this?

	Year
East	31.65	30.15	53.66	58.37	50.8
Middle	44.39	46.45	36.83	31.2	40.45
West	23.95	23.39	9.51	10.44	8.75

Re: Proc Report: Add Column for average of each row

PaigeMiller — Thu, 09 Dec 2021 18:14:38 GMT

Compute the average for each row before you get to PROC REPORT, so now you have a data set which has the yearly percents and another piece of data which has the average percents. Take into account the warning from @ballardw to compute the average percent properly, accounting for differences in the denominator each year.