BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

 

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

View solution in original post

6 REPLIES 6
ed_sas_member
Meteorite | Level 14

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
Pyrite | Level 9 Tal
Pyrite | Level 9

this works

Thank you so much!

ed_sas_member
Meteorite | Level 14

Awesome! You’re welcome @Tal 

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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
Meteorite | Level 14

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
Pyrite | Level 9 Tal
Pyrite | Level 9

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1011 views
  • 2 likes
  • 2 in conversation