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;
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;
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
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;
Great solution. Neat and transparent. Better than "cross join" solution on other forums.
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.