BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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.

2 REPLIES 2
Reeza
Super User

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

art297
Opal | Level 21

@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 943 views
  • 1 like
  • 3 in conversation