Pivot data on export

[Aidan]

Hi

I want to piviot the below data in excel on export.

Any ideas?

I know how to use proc export etc to do a data cump in excel but how would I pivot the data?

Col A	Age	Qtr	Gender	Type	Amt
E14	3	Quarter 3	F	IP	2
E14	5	Quarter 3	M	IP	1
E14	6	Quarter 3	F	DC	10
E14	7	Quarter 4	F	IP	1
E14	8	Quarter 4	M	DC	1
E15	3	Quarter 3	F	IP	2
E15	5	Quarter 3	M	IP	1
E15	6	Quarter 3	F	DC	10
E15	7	Quarter 4	F	IP	1
E15	8	Quarter 4	M	DC	1

Expected Output

Sum of Amt		Qtr	Gender	Type
		Quarter 3					Quarter 3  Total	Quarter 4				Quarter 4 Total	Grand Total
		F		F Total	M	M Total		F	F Total	M	M Total
Col A	Age	DC	IP		IP			IP		DC
E14	3		2	2			2						2
	5				1	1	1						1
	6	10		10			10						10
	7							1	1			1	1
	8									1	1	1	1
E15	3		2	2			2						2
	5				1	1	1						1
	6	10		10			10						10
	7							1	1			1	1
	8									1	1	1	1
Grand Total		20	4	24	2	2	26	2	2	2	2	4	30

[Reeza · Accepted solution]

ODS EXCEL + PROC TABULATE