Hello
I am using proc transpose.
In the result the order of columns is :YYMM_1901, YYMM_1902, YYMM_1904, YYMM_1903.
But I want to have order : YYMM_1901, YYMM_1902, YYMM_1903, YYMM_1904.
What is the way to control the order of the columns?
I know that I can use retain statement in a separate data step.
Is there a way to control columns order within proc transpose?
Data aaa;
input ID month sum_loan;
cards;
1 1901 10
1 1902 20
1 1904 30
2 1901 40
2 1903 50
;
run;
proc transpose data=aaa name=Metric prefix=YYMM_ out=bbb;
id month;
by ID ;
var sum_loan;
run;
The order of columns in a dataset is irrelevant, as columns are addressed by names.
Transpose adds the new columns whenever the first instance is encountered.
If you need a special order for display (eg in a report), you can prepare the order dynamically:
data aaa;
input ID month sum_loan;
cards;
1 1901 10
1 1902 20
1 1904 30
2 1901 40
2 1903 50
;
proc transpose data=aaa name=metric prefix=YYMM_ out=bbb;
id month;
by ID;
var sum_loan;
run;
proc sql noprint;
select name into :names separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'BBB' and name like 'YYMM%'
order by name;
quit;
proc print data=bbb noobs;
var id &names.;
run;
Data aaa;
input ID month sum_loan;
cards;
1 1901 10
1 1902 20
1 1904 30
2 1901 40
2 1903 50
;
run;
proc transpose data=aaa name=Metric prefix=YYMM_ out=bbb;
id month;
by ID ;
var sum_loan;
run;
proc transpose data=bbb(obs=0) out=temp;
var yymm_:;
run;
proc sql noprint;
select _NAME_ into : names separated by ' '
from temp
order by input(scan(_NAME_,-1,'_'),best.);
run;
data want;
retain id metric &names;
set bbb;
run;
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 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.