topic Re: proc transpose-order of columns of ID varaible in SAS Programming

proc transpose-order of columns of ID varaible

Ronein — Tue, 21 May 2019 05:46:10 GMT

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;

Re: proc transpose-order of columns of ID varaible

Kurt_Bremser — Tue, 21 May 2019 06:52:00 GMT

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;

Re: proc transpose-order of columns of ID varaible

Ksharp — Tue, 21 May 2019 12:51:09 GMT

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;