BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fongdo
Obsidian | Level 7

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

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

7 REPLIES 7
PeterClemmensen
Tourmaline | Level 20

@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;
fongdo
Obsidian | Level 7
Thank you very much @PeterClemmensen. It works, please advise what does ids {5} mean?
PeterClemmensen
Tourmaline | Level 20

It means that the array ids has 5 entries.

Ksharp
Super User

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;
fongdo
Obsidian | Level 7
Thank you very much Ksharp. It is very helpful.
PGStats
Opal | Level 21

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.

PG
fongdo
Obsidian | Level 7
Thank a lot PGStats.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 975 views
  • 5 likes
  • 4 in conversation