Help using Base SAS procedures

Transposing multiple variables

Reply
N/A
Posts: 0

Transposing multiple variables

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

          3

total_events_B_mth_3 post

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;

var total_events;

id fmtted_relative_mth_year;

run;

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.

Ask a Question
Discussion stats
  • 0 replies
  • 232 views
  • 0 likes
  • 1 in conversation