How do I changes the values ​​of the records in the table

[shlomiohana]

Hello,

I have this table:

Branch_Id	Rating
1	1
1	0
1	1
2	0
2	0
2	1
4	0
4	0
4	0
5	1
5	1
5	2

I want to go through the whole table and check that if a particular branch_id has more than one record with the number 0 in the Rating field it will replace it with 1 and will always leave a maximum of one record of the number 0 per branch.

No matter which records are replaced by the number 1 from all records with the number 0 in the same branch, the main thing is that one record remains with the number 0 in the ranking.

If the branch does not have a record with the number 0 as in branch 5 there is no need to make a change.

Example of the desired table after the change.

Final table:

Branch_Id	Rating
1	1
1	0
1	1
2	1
2	0
2	1
4	1
4	1
4	0
5	1
5	1
5	2

Thanks.

[Reeza]

data have;
input Branch_Id   Rating;
order = _n_;
cards;
1   1
1   0
1   1
2   0
2   0
2   1
4   0
4   0
4   0
5   1
5   1
5   2
;;;;

proc sort data=have;
by branch_ID rating;
run;

data want;
set have;
by branch_id rating;
original_rating = rating;
retain first_flag 0;

if first.branch_id then first_flag=0;

if first.rating and rating = 0 then first_flag = 1;
else if rating = 0 and first_flag = 1 then rating = 1;
run;

proc sort data=want;
by order;
run;

Using first logic is one way. There's probably a way to simplify this logic but this makes it pretty clear what's happening.

[Astounding]

Here's a one-step method that passes through each BRANCH data twice:

data want;
   changes_needed=-1;
   do until (last.branch_id);
      set have;
      by branch_id;
      if rating=0 then changes_needed + 1;
   end;
   do until (last.branch_id);
      set have;
      by branch_id;
      if rating=0 and changes_needed > 0 then do;
         rating=1;
         changes_needed = changes_needed - 1;
      end;
      output;
   end;
   drop changes_needed;
run;

The top DO loop counts how many zero values need to be changed for a BRANCH_ID.  Then the bottom DO loop rereads the same observations, makes the required changes, and outputs the observations.  

[ChrisNZ]

A quick logic:

data WANT;
  set HAVE;
  by BRANCH_ID;
  if first.BRANCH_ID then FOUND_ZERO=0;  
  if RATING=0 then FOUND_ZERO+1;
  if FOUND_ZERO>1 & RATING=0 then RATING=1;
run;