Create a do until loop to change values within a group until proportions are equivalent

[N8]

Hi all, 

I've been working on a solution to a problem and am hitting a wall. Would love some help if possible. Here's the scenario:

1. I have a data set with 3 variables (ID, Group, treatment), with both Group and Treatment being dichotomous ;

2. Based on these data points (below), the treatment rate for Group = 1 is 58% and the treatment rate for Group = 0 is 33%. 

3. I need to randomly change the treatment status for Group 1 members until they are +/- the 33% range for Group 0. in other words, I need to randomly change the treatment values for Group 1 until the global rate for both groups is equivalent. 

Doing this by hand is easy, but not feasible given the size of the data set i'm working with. Thank you for any help. i've tried this in Proc Optex and haven't had any success. If possible, just a simple Do Until Loop would be ideal as I could use a %let statement to set the target parameter. 

Here is the sample data set I used to create the above information. 

data Step1;
input ID Group treatment;
datalines;
1 1 1
6 1 1
9 1 1
10 1 0
18 1 0
20 1 1
21 1 1
24 1 0
25 1 0
26 1 1
34 1 0
35 1 1
2 0 0
3 0 0
4 0 1
5 0 1
7 0 1
8 0 1
11 0 0
12 0 0
13 0 0
14 0 0
15 0 0
16 0 0
17 0 1
19 0 1
22 0 0
23 0 0
27 0 0
28 0 0
29 0 1
30 0 0
31 0 0
32 0 0
33 0 0
36 0 1
37 0 0
38 0 0
39 0 1

;

run;

[PaigeMiller · Accepted solution]

Since there are 12 records in Group=1, and you want 33% to have the value of treatment=1 (that's 4 records with treatment=1), you simply assign a random number to each of the records in group 1, and then sort and the four records with the lowest random numbers are now assigned to have treatment=1;

data step2;
    set step1;
    if group=1 then random=rand('uniform');
run;
proc sort data=step2;
    by group rand;
run;
data want;
    set step2;
    by group;
    if first.group then counter=0;
    counter+1;
    if group=1 and counter <= 4 then treatment=1; 
    else treatment=0;
run;

[N8]

Yes - thank you. That works.

Paige - below is the script I was piecing together from random exchanges... I just couldn't figure out how to insert the 'by' function so that it would randomly change proportions for each group. Any chance you can see where I'm in error? It works perfectly for making global changes, I just can't seem to figure out how to do it for both groups. No worries if you're busy:

%let prob1 = .33;

data want;

set Step1 nobs=_tot;
if _n_ = 1 then do;
retain _need1 _remain;
_need1 = &prob1 * _tot ;                                       * number of 1 records needed, replace w/ specified %;
_remain = _tot;                                                      * remaining number of records;
end;
DesiredRate = ( ranuni( 12345 ) <= ( _need1 / _remain ) );
if DesiredRate then _need1 = _need1 - 1;            * if is 1, need 1 less 1;
_remain = _remain - 1;                                          * have 1 less record left;
drop _remain _need1;
run;

[PaigeMiller]

Sorry, but I don't think your approach is going to work without a lot of programming, and so I'm going to not answer your question.

[Shmuel]

Possible steps to be done:

1) count number of IDs per group using proc FREQ or any other tool.

2) compute desired amount to be treated, that is frequency * 33%

3) Use a random function to set computed amount for treatment=1;

proc freq data=step1;
  table group / out=temp1(keep=group count) ;
run;

data want;
  merge step1 temp1;
    by group;
       des = count * 33/100; /* computed desired amount of treatments */
        if rununi(1) * count le des then treatment=1;
        else treatment = 0
run;

/* checking step */
proc freq data=want;
   table group * treatment;
run;

Pay attention, ranuni function is given a seed argument. I used seed=1; you can change it.

As count in the test data in small you will find that the number of treatments may not fit the desired amount of treatments.