07-27-2015 11:56 AM
I have data at the month/year level with variables ID, target date, mth/year, mth/year distance from target date, total events for that mth/year, total type A events for that mth/year, and total type B events for that mth/year. All events are either type A or type B, that is, the last two columns will sum to the total events column.
ID target_date mth_year mth_year_relative total_events total_events_A total_events_B
1 2/2010 3/2010 +1 3 1 2
1 2/2010 2/2010 0 5 4 1
1 2/2010 1/2010 -1 7 7 0
What I want is a dataset with one row per ID and the column heads to indicate both the relative mth/year and whether the column is the count of total events, type A events, or type B events, like this:
ID target_date total_events_mth_3_pre total_events_mth_2_pre total_events_mth_1_pre total_events_mth_0
1 2/2010 7 5
total_events_mth_1_post total_events_mth_2_post total_events_mth_3_post total_events_A_mth_3_pre...etc through
Earlier, I only had the total events variable so I created a new variable based on relative mth_year to serve as the ID variable and transposed the variables to get what I wanted using this code:
proc transpose data=have out=want;
by ID target_date;
It worked perfectly, but now with the addition of the other two variables, I'm not sure how to transpose because I will have multiple values per by variable. Also note that I actually have 24 months pre and 24 months post, so if the solution involves typing out all the pre/post months by hand, that will start to get unwieldy.
Any help is much appreciated.