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
@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?
"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;
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?
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.