BookmarkSubscribeRSS Feed
aha123
Obsidian | Level 7

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;

4 REPLIES 4
art297
Opal | Level 21

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;

Ksharp
Super User
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

data_null__
Jade | Level 19

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;

neekum
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 9264 views
  • 3 likes
  • 5 in conversation