topic Shuffle the data rows available in SAS Programming

Shuffle the data rows available

Rajeshganta — Wed, 19 Feb 2020 17:25:02 GMT

I have a data like
Data
Set
Plan
View
As per my requirement the data has to get shuffled in a random way like any thing, may be like
Plan
Set
Data
View

Re: Shuffle the data rows available

PaigeMiller — Wed, 19 Feb 2020 17:46:32 GMT

Assign a random number to each row, then sort by the random number.

Re: Shuffle the data rows available

Reeza — Wed, 19 Feb 2020 18:05:36 GMT

PROC SURVEYSELECT?

Re: Shuffle the data rows available

Duggins — Wed, 19 Feb 2020 18:11:05 GMT

I agree with the PROC SURVEYSELECT suggestion. Setting your SAMPRATE to 100 ensures you get all the data back in your "sample" but the order will be randomized. Even if you had groups of data such as a training set, test set, and validation set, you could use stratified sampling in SURVEYSELECT to keep this a pretty efficient approach.

Re: Shuffle the data rows available

Watts — Wed, 19 Feb 2020 18:32:02 GMT

proc surveyselect data=have out=shuffled
     rate=1 outorder=random;

See doc here: OUTORDER=RANDOM

Re: Shuffle the data rows available

novinosrin — Wed, 19 Feb 2020 18:20:37 GMT

It's simple with RAND group of functions like @PaigeMiller suggested

/*Using SASHELP.CLASS*/
data want ;                           
  do _n_ = 1 to n ;                  
    p =   ceil (rand ("uniform") * n);         
    set sashelp.class point=p nobs=n ;
    output ;                          
  end ;                               
  stop ;                              
run ;

Re: Shuffle the data rows available

PeterClemmensen — Wed, 19 Feb 2020 18:23:22 GMT

Alternatively

proc sql;
   create table want as
   select * from sashelp.class
   order by rand('uniform');
quit;

Re: Shuffle the data rows available

PaigeMiller — Wed, 19 Feb 2020 18:23:33 GMT

@novinosrin wrote:

It's simple with RAND group of functions like @PaigeMiller suggested

/*Using SASHELP.CLASS*/
data want ;                           
  do _n_ = 1 to n ;                  
    p =   ceil (rand ("uniform") * n);         
    set sashelp.class point=p nobs=n ;
    output ;                          
  end ;                               
  stop ;                              
run ;

But data set WANT now has duplicates of some records, and other records are not present.

Re: Shuffle the data rows available

PaigeMiller — Wed, 19 Feb 2020 18:28:28 GMT

@Watts wrote:
proc surveyselect data=have out=shuffled;
     rate=1 outorder=random;
See doc here: OUTORDER=RANDOM

There is an error here, there should be no semi-colon after OUT=SHUFFLED

This solution fits my needs better than writing my own code, as I prefer to use SAS algorithms whenever possible (even in very simple cases), rather than writing my own, as I know SAS has taken the time to get it right and debugged it and tested it, and my own algorithms sometimes don't get it right.

Re: Shuffle the data rows available

Watts — Wed, 19 Feb 2020 18:31:18 GMT

@PaigeMiller Thanks for correcting my typo.

Re: Shuffle the data rows available

novinosrin — Wed, 19 Feb 2020 19:57:22 GMT

Sir @PaigeMiller Sorry, I overlooked the possibility of recurrence of the same random number. Good catch. But now, this tweak works, albeit boring


data want ;                                                                                                                                                                                                                                             
  array t [19] _temporary_ (1:19)  ;                                                                                                                                                                                                                   
  do until(_n=n) ;                                                                                                                    
    _n_ = ceil(rand ("uniform")*n);  
	if t(_n_)=. then continue;
    p = t(_n_);  
    set sashelp.class point=p nobs=n ;     
    _n+1;    
    output ;   
    t(_n_)=.; 
  end ;                                                                                                                                 
  stop ;   
  drop _:; 
run ;

Re: Shuffle the data rows available

PaigeMiller — Wed, 19 Feb 2020 20:01:06 GMT

May I ask why you go to such a complicated logic rather than the much simpler (and easier to understand) logic I originally stated; or the SQL solution above from @PeterClemmensen?

data want;
    set have;
    z=rand('uniform');
run;
proc sort data=want;
    by z;
run;

Re: Shuffle the data rows available

novinosrin — Wed, 19 Feb 2020 20:03:09 GMT

"why you go to such a complicated logic rather"- my dumb brain fails a lot of time. 😞

@PaigeMiller wrote:

May I ask why you go to such a complicated logic rather than the much simpler (and easier to understand) logic I originally stated; or the SQL solution above from @PeterClemmensen?
data want;
    set have;
    z=rand('uniform');
run;
proc sort data=want;
    by z;
run;