Generating numbers conditional on another column

[erchs]

My dataset contains a variable (X) with two categories, A and B. 

The data looks like this:

X

A

A

A

A

B

B

B

B

I would like to generate a new variable, Y, such that there will be a certain percentage of 1s, 2s, etc. within the categories of X. 

For the data above, I have 4 As, and 4 Bs. I want to create Y so that when X='A', Y will have 50% 1s and 50% 2s. 

Similarly, when X='B', Y will have 25% 3s and 75% 4s. 

For the table above, the final table would look like--

X Y

A 1

A 1

A 2 

A 2

B 4

B 3

B 4

B 4

Any suggestions or clue to solving this would be much appreciated. Thank you.

[v-shex-msft · Accepted solution]

Hi @erchs,

You can add a calculated column with RANDBETWEEN and SWITCH function to calculate the result:

Y = SWITCH([X],"A",SWITCH(RANDBETWEEN(0,1),0,1,2),"B",SWITCH(RANDBETWEEN(0,3),0,3,4),-1)

Regards,

Xiaoxin Sheng

[erchs]

For my general understanding, would you please explain how do the % of As, Bs and Cs are being taken into consideration here? Thank you.

[v-shex-msft]

Hi @erchs,

>> would you please explain how do the % of As, Bs and Cs are being taken into consideration here? 

For random 0 to 1, each item has 50% chance. it suitable for your request A.

 I want to create Y so that when X='A', Y will have 50% 1s and 50% 2s. 

For random between 0 to 3, each items has 25% chance. I setting one item equal to 3(1*25%) and others to 4(3*25%), so it is suitable for you request B.

when X='B', Y will have 25% 3s and 75% 4s. 

Regards,

Xiaoxin Sheng