How do you randomly and independently sort multiple columns?

[Jacob3]

Suppose I have the following data (in reality there are many more observations & the Z1, Z2, etc variables go to Z1000):

data have;
input Obs:32 ID:$8. Z1:32. Z2:32. Z3:32.;
datalines;
1 A 5 5 5
2 B 7.5 7.5 7.5
3 C 6.123 6.123 6.123
4 D 8 8 8
5 E 1 1 1
;
run;

The only thing I want to do is randomly sort each Z column, but importantly all of these sorts have to be independent of each other. An example of the ending dataset I would want:

1 A    8           1           6.123
2 B    6.123    7.5        5
3 C    7.5        5           1
4 D    5           6.123    8
5 E    1           8           7.5

If there were in fact only 3 'Z' columns I would just do the below:

data have2;

set have (keep =  Z1);

num = rand('normal',0,1);

run;

Then sort on num, create a count variable _N_ which I will use to left join back to the obs variable, then repeat twice more. However, given I have thousands of columns, this brute force method is not appealing. I am probably overlooking something. Any ideas?

[Reeza · Accepted solution]

• Transpose the data so that the columns are stacked, to a long data set
• Sort once using the random technique
• Add in counter for transpose/merge back
• Sort for transpose
• Transpose back to wide format
• Merge back with ID variables

data have;
infile cards dlm=' ';
input Obs : 32. ID :$8. Z1 :32. Z2 :32. Z3 :32.;
datalines;
1 A 5 5 5
2 B 7.5 7.5 7.5
3 C 6.123 6.123 6.123
4 D 8 8 8
5 E 1 1 1
;
run;

proc transpose data=have out=long;
by obs id;
run;

data random;
set long;
num = rand('normal',0,1);
run;

proc sort data=random (drop=obs);
by   _name_ num;
run;

data random2;
set random;
by _name_;
if first._name_ then obs=1;
else obs+1;
run;

proc sort data=random2; by obs;

proc transpose data=random2 out=wide2;
by obs;
var col1 ;
id _name_;
run;

data want;
merge have (keep=id obs) wide2;
by obs;
drop _name_;
run;

[Jacob3]

@Reeza , I should've come up with this myself :(. Thank you!