Proc Transpose and order of new columns

[kranikai_2]

Is it somehow possible to define in which order the new columns will appear in Proc Transpose's result?

[Peter_C]

kranikai_2

it is "possible" but probably "not easy".
(I've resorted to tricking transpose by prefixing the table with rows that provide non-missing value rows, ordered as I would like the columns and having a "key" I can use to remove that artificial data with a where clause on the OUT= of proc transpose..)

But it is easier to control order afterwards - depending on how you use the output from transpose - the standard method for small/modest data volumes uses a short data step [pre] data &output_dataset ;.
retain {required columns listed in required order} ;
set &input_dataset ;
run;[/pre] It is not great for performance - adding another pass through the data, but it is very "support-able"

peterC

[data_null__]

You may have noticed that the new variables are created in the same order they are found in the data. So you just need to make sure that the ID values are in the right order. But that can be difficult if the BY levels don't have rows for all the ID values.

If the data are from a summary you can use techniques to insure all LEVELS are created in the output. See COMPLETETYPES and PRELOADFMT in PROC SUMMAY or SPARSE option in PROC FREQ.

Sometimes that’s not an option; in that case I usually just create a special BY group that includes all values of the ID variable.

This example creates lots of data to make the point clear, I hope. It may not model your data very well but then you didn't give example data.

[pre]
proc plan seed=437500408;
factors
USUBJID = 10 ordered
year = 1 of 3
month = 4 of 12
y = 1 of 100
/ noprint;
output out=sime year nvals=(2007 2008 2009);
run;
data sime;
set sime;
date = mdy(month,1,year);
format date yymmn.;
run;
proc print;
run;
** The default PROC TRANSPOSE;
proc transpose data=sime out=wide1;
by USUBJID;
var y;
id date;
idlabel date;
run;
proc contents order=varnum;
run;

** Create special ID=. with values of DATE to establish the proper variable order.;
proc summary data=sime;
output out=dateinfo(drop=_:) min(date)=min max(date)=max;
run;
data sime2 / view=sime2;
if 0 then set sime;
set dateinfo;
do date = min to max;
output;
date = intnx('month',date,0,'end');
end;
do until(eof);
set sime end=eof;
output;
end;
stop;
run;
proc transpose data=sime2 out=wide2(where=(not missing(USUBJID)));
by USUBJID;
var y;
id date;
idlabel date;
run;
proc contents order=varnum;
run;
proc print;
run;
[/pre]

I've found if you copy and paste code that is bounded by the pre and /pre directives as above into EE the linefeeds are lost. If you first past to WORD then copy and paste to EE the linefeeds are preserved.

[kranikai_2]

I decided to add all the missing rows, with suitable 'null' values, and with my data, that works fine and was quite easy to do since I happen to have only one by variable there. (Might be more complicated if there were more of them.)