Transposing multiple variables

Walternate — Mon, 27 Jul 2015 15:12:32 GMT

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

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.

Re: Transposing multiple variables

Reeza — Mon, 27 Jul 2015 15:22:06 GMT

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

Re: Transposing multiple variables

art297 — Mon, 27 Jul 2015 22:19:25 GMT

@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

topic Transposing multiple variables in SAS Procedures

Transposing multiple variables

Re: Transposing multiple variables

Re: Transposing multiple variables