Help using Base SAS procedures

Transposing multiple variables

Reply
Frequent Contributor
Posts: 138

Transposing multiple variables

Hi,

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.

Super User
Posts: 19,780

Re: Transposing multiple variables

Posted in reply to Walternate

The macro from is ideal for your situation. http://support.sas.com/resources/papers/proceedings13/538-2013.pdf

Otherwise:

Transpose 3 times and merge

Transpose via Data Step

PROC Star
Posts: 7,468

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: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

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

            rename=(total_events_A=total_events_pre

          total_events_B=total_events_post ));

run;

%transpose(data=need,

           out=want,

           by=id target_date,

           id=fmtted_relative_mth_year,

           Guessingrows=1000,

           delimiter=_mth)

Art, CEO, AnalystFinder.com

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