How to re-assign column order in dataset?!
Proc Transpose generates column with order depends on the order of each value popping-up, see below.
Anyway to enforce order inside PROC?!
You have many choices.
1) Is to add an extra TX group that has all of the names in the order you want as the first BY group so it will be used to define the variables in the order you want. You can remove the extra observations with a WHERE= dataset option applied to the output dataset. So if NAME is the variable you used to have PROC TRANSPOSE generate those names then perhaps something like this.
proc sql;
create table for_transpose as
select distinct min(tx),name
from have
order by 1,2
;
quit;
data for_transpose;
set for_transpose(in=in1) have;
by tx;
if in1 then call missing(tx);
run;
proc transpose data=for_transpose out=want(where=(not missing(tx))) ;
by tx;
var mmti;
id name;
run;
2) Add another step AFTER the transpose to set the variable order.
data want;
retain tx mmti_1-mmti_8;
set have;
run;
3) Don't use PROC TRANSPOSE at all. Write your own data step and create your own variable names.
Here is a similar quest solved, https://communities.sas.com/t5/SAS-Programming/Transpose-column-with-chronological-order/td-p/817427
But my source data does not guarantees that A[/1] shows up first for each by variable(s).
You have many choices.
1) Is to add an extra TX group that has all of the names in the order you want as the first BY group so it will be used to define the variables in the order you want. You can remove the extra observations with a WHERE= dataset option applied to the output dataset. So if NAME is the variable you used to have PROC TRANSPOSE generate those names then perhaps something like this.
proc sql;
create table for_transpose as
select distinct min(tx),name
from have
order by 1,2
;
quit;
data for_transpose;
set for_transpose(in=in1) have;
by tx;
if in1 then call missing(tx);
run;
proc transpose data=for_transpose out=want(where=(not missing(tx))) ;
by tx;
var mmti;
id name;
run;
2) Add another step AFTER the transpose to set the variable order.
data want;
retain tx mmti_1-mmti_8;
set have;
run;
3) Don't use PROC TRANSPOSE at all. Write your own data step and create your own variable names.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.