topic Re: Randomly reorder rows within a group in Statistical Procedures

SURVEYSELECT: Randomly reorder rows within a group

spirto — Mon, 06 Feb 2017 15:13:01 GMT

Hi everyone, is it possible to use proc surveyselect to randomly reorder my rows of data within a group variable. For example, in the code below I would like to randomly reorder the rows of data within each site

data have;
input site $ sub $ x1 x2 x3;
datalines;
A A1 1 1 1
A A2 2 2 2
A A3 3 3 3
B B1 4 4 4
C C1 5 5 5
C C2 6 6 6
D D1 7 7 7
;

and i would like get a output data set that looks like this for example.

A A3 3 3 3

A A1 1 1 1
A A2 2 2 2
B B1 4 4 4

C C2 6 6 6
C C1 5 5 5
D D1 7 7 7

I tried using this code but it did not randomly order the rows.

proc surveyselect data=have out=want samprate=1;
    strata site;
    id _ALL_;
run;

Any help would be appreciated.

P.S. I was able to accomplish this using ranuni and then sorting but I would be prefer to use surveyselect in order to create many replicates and run a simulation.

data have; set have; r=ranuni(0); run;
proc sort data=have out=want; by site r; run;

Re: Randomly reorder rows within a group

LaurieF — Sun, 05 Feb 2017 20:30:22 GMT

I'll leave hints on how to use surveyselect to people who know what they're talking about. I'm the last one to ask!

But if you do have to go down the sorting route with ranuni, you can do it in a single pass:

proc sql;
create table want as
   select *
     from have
    order by site, ranuni(0);
quit;

You will get:

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

But that's the only downside. I've used this method over millions of rows and it's still very efficient.

Re: Randomly reorder rows within a group

spirto — Mon, 06 Feb 2017 22:18:26 GMT

Thank you Laurie. I didn't know you could order rows based on variables that are not in the select clause.