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 |
ODS EXCEL + PROC TABULATE
ODS EXCEL + PROC TABULATE
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.