topic Re: How do you randomly and independently sort multiple columns? in SAS Programming

How do you randomly and independently sort multiple columns?

Jacob3 — Fri, 27 May 2022 21:14:05 GMT

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?

Re: How do you randomly and independently sort multiple columns?

Reeza — Fri, 27 May 2022 21:34:55 GMT

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;

Re: How do you randomly and independently sort multiple columns?

Jacob3 — Fri, 27 May 2022 21:40:42 GMT

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