Help using Base SAS procedures

Transposing multiple variables

Frequent Contributor
Posts: 138

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


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;


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.

Super User
Posts: 23,776

Re: Transposing multiple variables

Posted in reply to Walternate

The macro from is ideal for your situation.


Transpose 3 times and merge

Transpose via Data Step

Posts: 8,167

Re: Transposing multiple variables

Posted in reply to Walternate

@Walternate: I agree with Fareeza, but do use the most recent version which can be downloaded from:

One thing we never fully incorporated into the macro, though, are dataset options. And, for what you want, they will be needed. As such, I'd use the macro as follows:

data need;

  set have (drop=mth_year mth_year_relative


          total_events_B=total_events_post ));




           by=id target_date,




Art, CEO,

Ask a Question
Discussion stats
  • 2 replies
  • 1 like
  • 3 in conversation