topic Re: How to randomly swap observations in particular column? in New SAS User

How to randomly swap observations in particular column?

fongdo — Sat, 23 Mar 2019 10:59:01 GMT

I would like to randomly swap data in column "state_id" (remain "permno") and generate new 1,000 datasets as example below. Could you kindly advise?

Original dataset

permno state_id

10000 6

10001 35

10002 2

10003 7

10004 5

New dataset

permno state_id

10000 7

10001 5

10002 2

10003 6

10004 35

Re: How to randomly swap observations in particular column?

PeterClemmensen — Sat, 23 Mar 2019 10:52:40 GMT

@fongdo Hi and welcome to the SAS Communities 🙂

There are several ways to do this. Here is one

data have;
input permno state_id;
datalines;
10000 6
10001 35
10002 2
10003 7
10004 5
;

data want(keep=permno state_id);

   array ids{5} _temporary_;
   seed=0;

   do i=1 by 1 until (eof1);
      set have end=eof1;
      ids[i]=state_id;
   end;

   call ranperm(seed, of ids[*]);

   do j=1 by 1 until (eof2);
      set have end=eof2;
      state_id=ids[j];
      output;
   end;

   stop;
run;

proc print data=have;run;
proc print data=want;run;

Re: How to randomly swap observations in particular column?

Ksharp — Sat, 23 Mar 2019 11:22:23 GMT

Make a macro to wrap the following code and repeat it 1000 times for your 1000 tables.

data have;
input permno state_id;
datalines;
10000 6
10001 35
10002 2
10003 7
10004 5
;


data x;
 set have(keep=state_id);
 id=rand('uniform');
run;
proc sort data=x;
 by id;
run;
data want;
 merge have(keep=permno) x;
 drop id;
run;

Re: How to randomly swap observations in particular column?

PGStats — Sat, 23 Mar 2019 18:52:14 GMT

Or put proc surveyselect to the task:

data have;
input permno state_id;
datalines;
10000 6
10001 35
10002 2
10003 7
10004 5
;

%let nreps=10;

proc surveyselect data=have(keep=state_id) out=rnd(keep=state_id) 
    samprate=100 method=srs reps=&nreps outorder=random;
run;

proc surveyselect data=have(drop=state_id) out=reps 
    samprate=100 method=srs reps=&nreps;
run;

data want;
set reps;
set rnd;
run;

Should be fairly efficient.

Re: How to randomly swap observations in particular column?

fongdo — Sun, 24 Mar 2019 03:05:10 GMT

Thank you very much @PeterClemmensen. It works, please advise what does ids {5} mean?

Re: How to randomly swap observations in particular column?

fongdo — Sun, 24 Mar 2019 03:07:32 GMT

Thank you very much Ksharp. It is very helpful.

Re: How to randomly swap observations in particular column?

fongdo — Sun, 24 Mar 2019 03:08:15 GMT

Thank a lot PGStats.

Re: How to randomly swap observations in particular column?

PeterClemmensen — Tue, 26 Mar 2019 15:30:52 GMT

It means that the array ids has 5 entries.