topic Re: proc transpose ID column sorting in SAS Programming

proc transpose ID column sorting

aha123 — Thu, 29 Dec 2011 13:48:10 GMT

In a2 output by the following steps, the order of D columns is D30, D10, D40, D20. Is there any option in proc transpose to make the ID columns sorted(i.e. D10, D20, D30, D40)

data a1;

input a b c;

cards;

1 30 100

2 10 200

2 40 201

3 20 300

;

run;

proc transpose data=a1 out=a2 prefix=d;

by a;

id b;

var c;

run;

proc transpose ID column sorting

art297 — Thu, 29 Dec 2011 14:08:25 GMT

You can always rearrange it with a short data step after you run the proc transpose:

data a2;

retain a d10 d20 d30 d40;

set a2;

run;

proc transpose ID column sorting

Ksharp — Fri, 30 Dec 2011 02:29:49 GMT

data a1;
input a b c;
cards;
1 30 100
2 10 200
2 40 201
3 20 300
;
run;
proc transpose data=a1 out=a2(drop=_name_) prefix=d;
by a;
id b;
var c;
run;  
proc sql noprint;
 select name into : list separated by ' '
  from dictionary.columns
   where libname='WORK' and memname='A2'
    order by input(substr(name,anydigit(name)),best32.);
quit;
%put &list;
data want;
  retain &list;
  set a2;
run;

Ksharp

Re: proc transpose ID column sorting

data_null__ — Fri, 30 Dec 2011 16:42:05 GMT

I prefer a proactive solution.

data a1;
   input a b c;
   cards;
1 30 100
2 10 200
2 40 201
3 20 300
;;;;
   run;
proc sort data=a1(keep=b)out=bframe nodupkey;
   by b;
   run;
data a1v / view=a1v;
   set bframe a1;
   run;
proc transpose data=a1V out=a2(where=(not missing(a))) prefix=d;
   by a;
   id b;
   var c;
   run;
proc print;
   run;

Re: proc transpose ID column sorting

neekum — Mon, 04 Apr 2016 00:12:21 GMT

Great solution. Neat and transparent. Better than "cross join" solution on other forums.