permute site assigment (derive all combinations)?

[kstolzmann]

Hello,

I have a dataset with subjects nested in 9 different sites and a variable "wave" where 3 sites are in each of 3 waves (simplified dataset below; SAS 9.4). What I want is to create n different variables/versions of "wave" such that all combinations of sites in waves are represented.  

Any help is greatly appreciated!

 
data have;
infile cards;
input subject site wave;
cards;
1 500 1
2 500 1 
3 600 1 
4 600 1
5 600 1
6 700 1
7 702 2 
8 702 2
9 702 2 
10 702 2
11 810 2
12 810 2
13 870 2 
14 870 2
15 870 2
16 870 2
17 911 3 
18 912 3
19 913 3
20 913 3
;
run;

[Reeza]

Please show what you expect as output. If you need to simplify the input that's fine.

[kstolzmann]

Thanks--what I would want, using a very simple example (using only 3 sites)

data want;
infile cards;
input subject site wave1 wave2 wave3 wave4 wave5 wave6;
cards;
1  500 1 1 2 2 3 3
3  600 2 3 1 3 2 1
17 911 3 2 3 1 1 2

;
run;

[ballardw]

---

@kstolzmann wrote:

Thanks--what I would want, using a very simple example (using only 3 sites)

data want;
infile cards;
input subject site wave1 wave2 wave3 wave4 wave5 wave6;
cards;
1 500 1 1 2 2 3 3
2 600 2 3 1 3 2 1
3 911 3 2 3 1 1 2

;
run;

---

Since SUBJECT=2 has a SITE=500 in your example data you will have to supply the logic why the output for SUBJECT=2 now has a Site=600.

Of if the actual subject number is not important let us know.

[kstolzmann]

Ah, thanks, I edited my post (I'm further confusing the question!)--subjects do need to stay within their site, but the randomization (wave assignment) happens at the site level. 

[Reeza]

---

@kstolzmann wrote:

Thanks--what I would want, using a very simple example (using only 3 sites)

data want;
infile cards;
input subject site wave1 wave2 wave3 wave4 wave5 wave6;
cards;
1 500 1 1 2 2 3 3
2 600 2 3 1 3 2 1
3 911 3 2 3 1 1 2

;
run;

---

How are the wave1, wave2, wave3 values calculated?

[kstolzmann]

The wave1, wave2...waven variables are assigned randomly such that (in my original post) 3 sites are assigned to wave=1, 3 sites are assigned to wave=2, and 3 sites to wave=3. But I'm looking to generate these assignments for all combinations (9!/3! 3! 3! = n=1680 "wave" variables...eep).

[Reeza]

https://blogs.sas.com/content/iml/2010/10/13/generate-all-permutations-in-sas.html

[kstolzmann]

Thanks, this is very helpful. I need to figure out how to assign each of the numbers (1,2,3) to each site such that all combinations are produced but I will play around with proc iml.

[Astounding]

See if this is exactly what you are seeking:

proc sql;
   create table site as select distinct site from have;
   create table wave as select distinct wave from have;
   create table want as select * from site, wave;
quit;

If you are looking to apply these combinations to every SUBJECT, the same techniques can be extended pretty easily.