topic Re: Fill variables to reach a total in New SAS User

Fill variables to reach a total

NewUsrStat — Mon, 16 Dec 2024 17:11:43 GMT

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

Re: Fill variables to reach a total

PaigeMiller — Mon, 16 Dec 2024 15:13:24 GMT

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.

Re: Fill variables to reach a total

Kurt_Bremser — Mon, 16 Dec 2024 15:23:33 GMT

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;

Re: Fill variables to reach a total

ballardw — Mon, 16 Dec 2024 15:24:48 GMT

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.

Re: Fill variables to reach a total

NewUsrStat — Mon, 16 Dec 2024 16:24:56 GMT

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.

Re: Fill variables to reach a total

NewUsrStat — Mon, 16 Dec 2024 17:07:11 GMT

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.

Re: Fill variables to reach a total

ballardw — Mon, 16 Dec 2024 16:54:02 GMT

@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.

Re: Fill variables to reach a total

NewUsrStat — Mon, 16 Dec 2024 17:05:46 GMT

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 -->

Re: Fill variables to reach a total

PaigeMiller — Mon, 16 Dec 2024 17:21:23 GMT

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.

Re: Fill variables to reach a total

NewUsrStat — Mon, 16 Dec 2024 17:33:44 GMT

Ok sure, a new post will come soon