I have a number of different tables containing information on college students, such as their GPA at the end of each year, their major each year, amounts of different kinds of financial aid they took, cumulative credit hours, etc. Right now this exists in separate tables by topic (major and credits, financial aid, background info, etc.), with one row for each academic year. I'd like to transpose every variable in some of these tables so that instead of having anywhere from 1-8 rows per student in each table (depending on how long they were in school), we have a set of columns for each academic year 1 through 8. So, for example, the data I'm starting with looks like this: StudentID AcademicYear CumGPA EdLevel Major AidAmount
1000 1 3.20 Freshman BasketWeaving $1,800 1002 1 2.77 Freshman SpaceAccounting $3,600 1002 2 2.93 Sophomore SpaceAccounting $933 1002 3 3.11 Junior SpaceAccounting $0 1002 4 3.03 Senior SpaceAccounting $1,200 1003 3 2.46 Junior Journalism $645 1003 4 2.75 Senior Journalism $4,300 1004 1 3.30 Freshman CryptoZoology $10,500 1004 2 2.73 Sophomore CryptoZoology $4,000 1004 3 2.34 Sophomore Biology $1,175 I want my output to look like this: StudentID CumGPA_1 EdLevel_1 Major_1 AidAmount_1 CumGPA_2 EdLevel_2 Major_2 AidAmount_2 CumGPA_3 EdLevel_3 Major_3 AidAmount_3 CumGPA_4 EdLevel_4 Major_4 AidAmount_4
1000 3.20 Freshman BasketWeaving $1,800 1002 2.77 Freshman SpaceAccounting $3,600 2.93 Sophomore SpaceAccounting $933 3.11 Junior SpaceAccounting $0 3.03 Senior SpaceAccounting $1,200 1003 2.46 Junior Journalism $645 2.75 Senior Journalism $4,300 1004 3.30 Freshman CryptoZoology $10,500 2.73 Sophomore CryptoZoology $4,000 2.34 Sophomore Biology $1,175 The only way I can think of to do this with proc transpose involves rotating each of the above variables into new datasets individually (by StudentID with id AcademicYear), and then merging them all afterwards. I don't find that to be very practical given that I would have to do this for dozens of variables, and that we're expecting to receive additional data later in the year that will require everything to be done a second time. I presume there are ways to do this with arrays in a datastep, but the examples of array code I have found are usually making the opposite transformation (from wide to long), and don't explain the steps very welll. Would someone be able to help me with this, or point me to resources that outline the steps and functions that I would need to do this in a very concice way?
... View more