topic Re: will "do until" work here? in SAS Procedures

will "do until" work here?

Tal — Sun, 16 Feb 2020 18:06:49 GMT

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;

Re: will "do until" work here?

ed_sas_member — Sun, 16 Feb 2020 19:48:07 GMT

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;

Re: will "do until" work here?

Tal — Sun, 16 Feb 2020 20:05:59 GMT

this works

Thank you so much!

Re: will "do until" work here?

ed_sas_member — Sun, 16 Feb 2020 20:35:59 GMT

Awesome! You’re welcome @Tal

Re: will "do until" work here?

Tal — Thu, 20 Feb 2020 16:47:37 GMT

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

Re: will "do until" work here?

ed_sas_member — Thu, 20 Feb 2020 18:16:59 GMT

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;

Re: will "do until" work here?

Tal — Thu, 20 Feb 2020 19:28:53 GMT

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