DATA Step, Macro, Functions and more

proc transpose ID column sorting

Reply
Contributor
Posts: 57

proc transpose ID column sorting

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 Star
Posts: 7,360

proc transpose ID column sorting

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;

Super User
Posts: 9,676

proc transpose ID column sorting

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

Respected Advisor
Posts: 3,777

Re: proc transpose ID column sorting

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;

New Contributor
Posts: 3

Re: proc transpose ID column sorting

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

Ask a Question
Discussion stats
  • 4 replies
  • 4277 views
  • 3 likes
  • 5 in conversation