topic Re: How can I split values between two different lines of a table? in SAS Data Management https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912339#M20909 <P>The solution&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/13674">@LinusH</a>&nbsp;proposes should work for an implementation using DIS.</P> <P>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&nbsp;<EM>initial_date</EM>&nbsp;and <EM>final_date</EM> to the grouping?</P> Sat, 20 Jan 2024 01:52:40 GMT Patrick 2024-01-20T01:52:40Z How can I split values between two different lines of a table? https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912152#M20904 <P>This is my current table:</P> <TABLE> <TBODY> <TR> <TD>Year</TD> <TD>Month</TD> <TD>Person</TD> <TD>Initial_Date</TD> <TD>Final_Date</TD> <TD>Unities</TD> <TD>Price</TD> <TD>Value</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>01-01-2024</TD> <TD>05-01-2024</TD> <TD>7</TD> <TD>30,28</TD> <TD>1,000292</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>06-01-2024</TD> <TD>15-01-2024</TD> <TD>7</TD> <TD>30,28</TD> <TD>1,000292</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>05-01-2024</TD> <TD>10-01-2024</TD> <TD>13</TD> <TD>122,54</TD> <TD>3,00038</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>11-01-2024</TD> <TD>19-01-2024</TD> <TD>13</TD> <TD>122,54</TD> <TD>3,00038</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>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.</P> <P>&nbsp;</P> <P>I want the final result to have the values splited between the 2 rows instead of being repeated. Such as:</P> <TABLE> <TBODY> <TR> <TD>Year</TD> <TD>Month</TD> <TD>Person</TD> <TD>Initial_Date</TD> <TD>Final_Date</TD> <TD>Unities</TD> <TD>Price</TD> <TD>Value</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>01-01-2024</TD> <TD>05-01-2024</TD> <TD>3</TD> <TD>15,14</TD> <TD>0,500146</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>06-01-2024</TD> <TD>15-01-2024</TD> <TD>4</TD> <TD>15,14</TD> <TD>0,500146</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>05-01-2024</TD> <TD>10-01-2024</TD> <TD>6</TD> <TD>61,27</TD> <TD>1,50019</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>11-01-2024</TD> <TD>19-01-2024</TD> <TD>7</TD> <TD>61,27</TD> <TD>1,50019</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>How do I do this in SAS Data Integration Studio?</P> <P>&nbsp;</P> <P>Thanks for the help</P> Fri, 19 Jan 2024 10:57:03 GMT https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912152#M20904 InêsMaximiano 2024-01-19T10:57:03Z Re: How can I split values between two different lines of a table? https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912154#M20905 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)?<BR />Not sure if this will depict "the truth", so you probably need to document this rule for data conumers.<BR />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.<BR />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. Fri, 19 Jan 2024 11:20:43 GMT https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912154#M20905 LinusH 2024-01-19T11:20:43Z Re: How can I split values between two different lines of a table? https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912156#M20906 <BLOCKQUOTE><HR /><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/417025">@InêsMaximiano</a>&nbsp;wrote:<BR /> <P>This is my current table:</P> <TABLE> <TBODY> <TR> <TD>Year</TD> <TD>Month</TD> <TD>Person</TD> <TD>Initial_Date</TD> <TD>Final_Date</TD> <TD>Unities</TD> <TD>Price</TD> <TD>Value</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>01-01-2024</TD> <TD>05-01-2024</TD> <TD>7</TD> <TD>30,28</TD> <TD>1,000292</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>06-01-2024</TD> <TD>15-01-2024</TD> <TD>7</TD> <TD>30,28</TD> <TD>1,000292</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>05-01-2024</TD> <TD>10-01-2024</TD> <TD>13</TD> <TD>122,54</TD> <TD>3,00038</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>11-01-2024</TD> <TD>19-01-2024</TD> <TD>13</TD> <TD>122,54</TD> <TD>3,00038</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>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.</P> <P>&nbsp;</P> <P>I want the final result to have the values splited between the 2 rows instead of being repeated. Such as:</P> <TABLE> <TBODY> <TR> <TD>Year</TD> <TD>Month</TD> <TD>Person</TD> <TD>Initial_Date</TD> <TD>Final_Date</TD> <TD>Unities</TD> <TD>Price</TD> <TD>Value</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>01-01-2024</TD> <TD>05-01-2024</TD> <TD>3</TD> <TD>15,14</TD> <TD>0,500146</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Mary</TD> <TD>06-01-2024</TD> <TD>15-01-2024</TD> <TD>4</TD> <TD>15,14</TD> <TD>0,500146</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>05-01-2024</TD> <TD>10-01-2024</TD> <TD>6</TD> <TD>61,27</TD> <TD>1,50019</TD> </TR> <TR> <TD>2024</TD> <TD>1</TD> <TD>Peter</TD> <TD>11-01-2024</TD> <TD>19-01-2024</TD> <TD>7</TD> <TD>61,27</TD> <TD>1,50019</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>How do I do this in SAS Data Integration Studio?</P> <P>&nbsp;</P> <P>Thanks for the help</P> <HR /></BLOCKQUOTE> <P>Are there ALWAYS two rows for each person? Can there ever be three (or more) rows for a person?</P> Fri, 19 Jan 2024 11:25:31 GMT https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912156#M20906 PaigeMiller 2024-01-19T11:25:31Z Re: How can I split values between two different lines of a table? https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912158#M20907 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. Fri, 19 Jan 2024 11:33:57 GMT https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912158#M20907 InêsMaximiano 2024-01-19T11:33:57Z Re: How can I split values between two different lines of a table? https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912159#M20908 <P>"Skeleton" program, untested:</P> <PRE><CODE class=" language-sas">data &amp;_OUTPUT.; set &amp;_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; </CODE></PRE> Fri, 19 Jan 2024 11:45:20 GMT https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912159#M20908 LinusH 2024-01-19T11:45:20Z Re: How can I split values between two different lines of a table? https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912339#M20909 <P>The solution&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/13674">@LinusH</a>&nbsp;proposes should work for an implementation using DIS.</P> <P>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&nbsp;<EM>initial_date</EM>&nbsp;and <EM>final_date</EM> to the grouping?</P> Sat, 20 Jan 2024 01:52:40 GMT https://communities.sas.com/t5/SAS-Data-Management/How-can-I-split-values-between-two-different-lines-of-a-table/m-p/912339#M20909 Patrick 2024-01-20T01:52:40Z