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.
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
@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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.