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;
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;
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;
this works
Thank you so much!
Awesome! You’re welcome @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
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;
ahh I was trying "by accnt||age" and that's why was not working for me. Thank you so much!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.