will "do until" work here?

[Tal]

hello SAS experts 

this is just a  piece of my code and  sum_opp which is the sum of opp per account is always>4 but i need to cap i to 4 meaning for the  first account   i need to overwrite one "1" for opp  from any of the 5 records to "0"

or for the second account i need to overwrite opp=1 to opp=0 in any two records and so on....

I m  trying  this code below but not working, not even sure it is correct .Can anyone pls assist me?  Thx

data temp;
input accnt name sale opp sum_opp;
cards;
100 miki 1 1 5
100 maya 1 1 5
100 mono 0 1 5
100 vera 1 1 5
100 bono 0 1 5
101 riki 1 1 6
101 zara 1 1 6
101 rono 0 1 6
101 vera 1 1 6
101 xue 0 1 6
101 xu 0 1 6
;
proc sort data=temp;by accnt; run;

data temp2;
set temp;
by accnt;
count=1;
do until(count>sum_opp-4);
if opp=1 then do; opp=0; count=count+1;end;
else do; opp=opp;count=count; end;
end;
run;

[ed_sas_member · Accepted solution]

Hi @Tal 

When you say « I need to overwrite one "1" for opp  from any of the 5 records to "0" », how do you choose the records to modify? As data are not sorted by name inside accounts, does it matter to choose the last records by default ?

if not, does this code make sense?

data temp2;
   set temp;
   by accnt;
   if first.accnt then count=0;
   if opp=1 then count+1;
   if count > 4 then opp=0;
 run;

[Tal]

this works

Thank you so much!

[ed_sas_member]

Awesome! You’re welcome @Tal 

[Tal]

hello ed_sas_member,

and  what if I did not  have to overwrite opp randomly, what if  lets  say  the oldest holders within an account should be set to "0"

if   I have the  age in my data and  lets  say  zara and  riki  from  the account 101  are the oldest  and  I want them set to 0?

Thx 

[ed_sas_member]

Hi @Tal 

Here is an approach to do this:

data temp;
input accnt name $ age sale opp sum_opp;
cards;
100 miki 30 1 1 5 
100 maya 25 1 1 5
100 mono 65 0 1 5
100 vera 56 1 1 5
100 bono 28 0 1 5
101 riki 80 1 1 6 
101 zara 79 1 1 6
101 rono 60 0 1 6
101 vera 30 1 1 6
101 xue 40 0 1 6
101 xu 60 0 1 6
;

data temp_id;
	set temp;
	id+1; /*set a unique id for each row*/
run;

proc sort data=temp_id;
	by accnt age;
run;

data temp2;
   set temp_id;
   by accnt age;
   if first.accnt then count=0;
   if opp=1 then count+1;
   if count > 4 then opp=0;
run;

proc sort data=temp2;
	by id; /*sort data to have them in the initial order*/
run;

[Tal]

ahh I was  trying  "by accnt||age" and that's  why was not working for me. Thank you so much!