Fill variables to reach a total

[NewUsrStat]

Hi guys, 

suppose to have the following dataset: 

data DB;
  input ID :$20. Total Class1 Class2 Class3;
cards;
0001 1.34    0.2     .      .
0001   .       .    0.5     . 
0002   9      1.2    .      . 
0003   2      .    0.5      .
; 
run;

The sum Class1-3 is < Total. 

Is there a way to get the following? 

data DB1;
  input ID :$20. Total Class1 Class2 Class3;
cards;
0001 1.34    0.2     .      .
0001   .       .    0.5     0.64 
0002   9      1.2    5      2.8 
0003   2      .    0.5      2.5
; 
run;

In other words, I need to reach the "Total" by adding values to class* variables from the last filled class* variable  until the last class* variable (i.e., Class3) but with the rule that each Class variable cannot have a value >5. 

Thank you in advance

[PaigeMiller]

More explanation is needed. In the second row, total is missing, how do you determine to add 0.64 to that row?

More explanation is needed for row 1 as well.

[NewUsrStat]

So: 0.64 is added because 1.34-(0.2 + 0.5). Moreover 0.64 MUST (!) be added at Class3 because it is the nearest class after the last filled class that is Class2. No jumping is allowed.

[ballardw]

---

@NewUsrStat wrote:
So: 0.64 is added because 1.34-(0.2 + 0.5). Moreover 0.64 MUST (!) be added at Class3 because it is the nearest class after the last filled class that is Class2. No jumping is allowed.

---

Which immediately brings up the question of what to do if the "total" still needs to be added bu the last value is a Class3, and then what if class1, class2 and class3 all have values.

1 is just as near to 2 as 3 last time I checked. So it appears you may have some more RULES to provide.

[NewUsrStat]

1) "what to do if the "total" still needs to be added but the last value is a Class3,": it is not the case. Class3 is shown to simplify the coding but I have until Class7; but in any case there is always at least one empty class after the last not empty class. The idea is to fill from the last not empty class on.
2)" if class1, class2 and class3 all have values" not a problem because the filling should go from the last filled on -->

[PaigeMiller]

Sorry, this explanation is very scattered across different posts, and still seems to be confusing to me.

Please put all of the explanation of the logic of this problem in one new post, that covers as clearly as you can the real situation that you are working on. I am asking for  complete and clear writing, leaving nothing out; I am not asking you to write about SAS or code.

[NewUsrStat]

Ok sure, a new post will come soon

[Kurt_Bremser]

First, let's get rid of the redundancy and get one observation per id:

proc summary data=db;
by id;
var total class:;
output out=have (drop=_type_ _freq_) max()=;
run;

Then, sum up using an array and the OF keyword in the SUM function wherever used:

data want;
set have;
array c{*} class:;
do i = 1 to dim(c) until (sum(of c{*}) ge total);
  if c{i} = . then c{i} = min(5,total - sum(of c{*}));
end;
run;

[NewUsrStat]

Thank you very much Kurt for your help. Unfortunately I cannot remove the redundancy because the matrix is much more complicated than what I showed and other information is present justifying the redundancy. Moreover your code assumes that the classes before the last non empty class are all filled but it is not always the case. I will edit my question with an additional case if I can.

[ballardw]

Since this looks more than a little bit like the output from the request in this thread: https://communities.sas.com/t5/New-SAS-User/From-long-format-to-short-data-format/m-p/953512

Perhaps it would make more sense to do this "add" BEFORE making the data set wider. At least it seem like it.

Which means it might also be time to talk about the complete "start" data and what the planned result might be. Things that do one step at a time from request often lead to more complex than needed results because it may be that other procedures will combine multiple steps into one.