How can I split values between two different lines of a table?

[InêsMaximiano]

This is my current table:

Year	Month	Person	Initial_Date	Final_Date	Unities	Price	Value
2024	1	Mary	01-01-2024	05-01-2024	7	30,28	1,000292
2024	1	Mary	06-01-2024	15-01-2024	7	30,28	1,000292
2024	1	Peter	05-01-2024	10-01-2024	13	122,54	3,00038
2024	1	Peter	11-01-2024	19-01-2024	13	122,54	3,00038

The number columns are the sum of the values for each Year/Month, but they are repeated because of the different columns Initial_Date and Final_Date.

I want the final result to have the values splited between the 2 rows instead of being repeated. Such as:

Year	Month	Person	Initial_Date	Final_Date	Unities	Price	Value
2024	1	Mary	01-01-2024	05-01-2024	3	15,14	0,500146
2024	1	Mary	06-01-2024	15-01-2024	4	15,14	0,500146
2024	1	Peter	05-01-2024	10-01-2024	6	61,27	1,50019
2024	1	Peter	11-01-2024	19-01-2024	7	61,27	1,50019

How do I do this in SAS Data Integration Studio?

Thanks for the help

[LinusH]

So you want to split Price and Value in exactly halves, and Units halves rounded to the lower value for the first record (and upper for the second record)?
Not sure if this will depict "the truth", so you probably need to document this rule for data conumers.
From a data integrity perspective it would make more sense to merge the records instead, or try to find the original records that contributes to your numerival columns with date specifications.
That being said - there's not really an OOTB transformation for this, so probably a User Written data step is required. While doing so, make sure you use as many of the DI Studio generated macro variables in your code to make it more flexible and resilant for changes over time.

[PaigeMiller]

---

@InêsMaximiano wrote:

This is my current table:

Year	Month	Person	Initial_Date	Final_Date	Unities	Price	Value
2024	1	Mary	01-01-2024	05-01-2024	7	30,28	1,000292
2024	1	Mary	06-01-2024	15-01-2024	7	30,28	1,000292
2024	1	Peter	05-01-2024	10-01-2024	13	122,54	3,00038
2024	1	Peter	11-01-2024	19-01-2024	13	122,54	3,00038

The number columns are the sum of the values for each Year/Month, but they are repeated because of the different columns Initial_Date and Final_Date.

I want the final result to have the values splited between the 2 rows instead of being repeated. Such as:

Year	Month	Person	Initial_Date	Final_Date	Unities	Price	Value
2024	1	Mary	01-01-2024	05-01-2024	3	15,14	0,500146
2024	1	Mary	06-01-2024	15-01-2024	4	15,14	0,500146
2024	1	Peter	05-01-2024	10-01-2024	6	61,27	1,50019
2024	1	Peter	11-01-2024	19-01-2024	7	61,27	1,50019

How do I do this in SAS Data Integration Studio?

Thanks for the help

---

Are there ALWAYS two rows for each person? Can there ever be three (or more) rows for a person?

[InêsMaximiano]

The table has either one or two rows for each person. I need to fix it when we have two rows. There is never more than two rows for each person.

[LinusH]

"Skeleton" program, untested:

data &_OUTPUT.;
   set &_INPUT.;
   by year month person;
   if first.person and not last.person then do;
      price = price / 2;
      value = value / 2;
      unities = int(unities / 2);
   end;
   else if last.person and not first.person then do;
      price = price / 2;
      value = value / 2;
      unities = ceil(unities / 2);
   end;
run;

[Patrick]

The solution @LinusH proposes should work for an implementation using DIS.

Why do you have to deal with such a source table in first place? Is this the result of some earlier SQL/group by aggregation where you potentially would need to add additional variables like initial_date and final_date to the grouping?