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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.