Proc Transpose Long to Wide

[bnd]

Hello,

I am trying to proc transpose 3 datasets from long to wide (I am transposing one dataset at a time). I have data at 5 different time points (baseline, Month 4, Month 10, Month 16, and Month 22). For some of my data, if a person is missing a timepoint, it is not listed in the dataset. For example, Person 1234 could have data for baseline, month 4, month 10, and month 22 (but not Month 16). For some reason, it takes the data from month 22 and puts in month 16, so now month 22 is missing data. I am not sure why this may be happening. Any thoughts? Is there something that I need to add to the code to prevent this from happening. My code is provided below. 

proc transpose data = builtenv.ppaq_v1 out=builtenv.ppaq_v1_wide_mod (drop = _NAME_ rename=(col1=mod_act_baseline col2=mod_act_m_4 col3=mod_act_m_10 col4=mod_act_m_16 col5=mod_act_m_22));
var paq8_avg_mod;
by acrostic;
run;

[PGStats · Accepted solution]

Don't count on column ordering to define the new variable names. Use your time point variable instead. Transpose all your data together, so that every possible time point will get a column. Start with something like this:

data ppaq / view=ppaq;
set builtenv.ppaq_v* indsname=ds;
length dataset $32;
dataset = scan(ds, 2, ".");
run;

proc transpose data = ppaq out=ppaq_wide_mod (drop = _NAME_));
var paq8_avg_mod;
by dataset acrostic notsorted;
id timePoint;
run;

then use BY processing to analyse your data.

[bnd]

Thanks so much, @PGStats, it worked!