topic Re: Animated Bubble Chart with years interpolated in SAS Programming https://communities.sas.com/t5/SAS-Programming/How-to-interpolate-data-set-with-by-statement/m-p/596403#M171713 <P>1. Try reducing your problem into something more concise</P> <P>2. Provide the data as a data step</P> <P>3. Avoid attaching MS Office files</P> Tue, 15 Oct 2019 01:29:22 GMT ChrisNZ 2019-10-15T01:29:22Z How to interpolate data set with by statement https://communities.sas.com/t5/SAS-Programming/How-to-interpolate-data-set-with-by-statement/m-p/596141#M171604 <PRE><CODE class=" language-sas">proc expand data=interpoled_dataout=interpoled_data from=year; by cluster; id year2; convert emp / method=join; run;</CODE></PRE><P>I have two data points for two years (1990 and 2018) for three clusters:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>year1</TD><TD>cluster</TD><TD>emp</TD></TR><TR><TD>01JAN1990</TD><TD>Accommodation and Food Svcs</TD><TD>12868</TD></TR><TR><TD>01JAN2018</TD><TD>Accommodation and Food Svcs</TD><TD>31869.5</TD></TR><TR><TD>01JAN1990</TD><TD>Advanced Materials</TD><TD>7477.417</TD></TR><TR><TD>01JAN2018</TD><TD>Advanced Materials</TD><TD>3339.25</TD></TR><TR><TD>01JAN1990</TD><TD>Agribusiness</TD><TD>5567.5</TD></TR><TR><TD>01JAN2018</TD><TD>Agribusiness</TD><TD>3619.5</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I want to interpolate the data for each cluster from 1990 - 2018, (i.e. 1990, 1991, 1992, ...., 2018) for each cluster, such that:</P><P>Accommodation and Food Svcs 1990 emp</P><P>Accommodation and Food Svcs 1991 emp = 1990 emp + (2018 emp - 1990 emp) / 29 years</P><P>.</P><P>.</P><P>.</P><P>Accommodation and Food Svcs 2017 emp = 2016 emp + (2018 emp - 1990 emp) / 29 years</P><P>Accommodation and Food Svcs 2018 emp</P><P>Advance Materials 1990 emp</P><P>Advance Materials 1991 emp = 1990 emp + (2018 emp - 1990 emp) / 29 years</P><P>.</P><P>.</P><P>.</P><P>Advance Materials 2017 emp = 2016 emp + (2018 emp - 1990 emp) / 29 years</P><P>Advance Materials 2018 emp</P><P>Agribusiness 1990 emp</P><P>Agribusiness 1991 emp = 1990 emp + (2018 emp - 1990 emp) / 29 years</P><P>.</P><P>.</P><P>.</P><P>Agribusiness 2017 emp = 2016 emp + (2018 emp - 1990 emp) / 29 years</P><P>Agribusiness 2018 emp</P><P>&nbsp;</P><P>Desired result:</P><TABLE><TBODY><TR><TD>year1</TD><TD>cluster</TD><TD>emp</TD></TR><TR><TD>01JAN1990</TD><TD>Accommodation and Food Svcs</TD><TD>12868</TD></TR><TR><TD>01JAN1991</TD><TD>Accommodation and Food Svcs</TD><TD>13523.22</TD></TR><TR><TD>01JAN1992</TD><TD>Accommodation and Food Svcs</TD><TD>14178.45</TD></TR><TR><TD>01JAN1993</TD><TD>Accommodation and Food Svcs</TD><TD>14833.67</TD></TR><TR><TD>01JAN1994</TD><TD>Accommodation and Food Svcs</TD><TD>15488.9</TD></TR><TR><TD>01JAN1995</TD><TD>Accommodation and Food Svcs</TD><TD>16144.12</TD></TR><TR><TD>01JAN1996</TD><TD>Accommodation and Food Svcs</TD><TD>16799.34</TD></TR><TR><TD>01JAN1997</TD><TD>Accommodation and Food Svcs</TD><TD>17454.57</TD></TR><TR><TD>01JAN1998</TD><TD>Accommodation and Food Svcs</TD><TD>18109.79</TD></TR><TR><TD>01JAN1999</TD><TD>Accommodation and Food Svcs</TD><TD>18765.02</TD></TR><TR><TD>01JAN2000</TD><TD>Accommodation and Food Svcs</TD><TD>19420.24</TD></TR><TR><TD>01JAN2001</TD><TD>Accommodation and Food Svcs</TD><TD>20075.47</TD></TR><TR><TD>01JAN2002</TD><TD>Accommodation and Food Svcs</TD><TD>20730.69</TD></TR><TR><TD>01JAN2003</TD><TD>Accommodation and Food Svcs</TD><TD>21385.91</TD></TR><TR><TD>01JAN2004</TD><TD>Accommodation and Food Svcs</TD><TD>22041.14</TD></TR><TR><TD>01JAN2005</TD><TD>Accommodation and Food Svcs</TD><TD>22696.36</TD></TR><TR><TD>01JAN2006</TD><TD>Accommodation and Food Svcs</TD><TD>23351.59</TD></TR><TR><TD>01JAN2007</TD><TD>Accommodation and Food Svcs</TD><TD>24006.81</TD></TR><TR><TD>01JAN2008</TD><TD>Accommodation and Food Svcs</TD><TD>24662.03</TD></TR><TR><TD>01JAN2009</TD><TD>Accommodation and Food Svcs</TD><TD>25317.26</TD></TR><TR><TD>01JAN2010</TD><TD>Accommodation and Food Svcs</TD><TD>25972.48</TD></TR><TR><TD>01JAN2011</TD><TD>Accommodation and Food Svcs</TD><TD>26627.71</TD></TR><TR><TD>01JAN2012</TD><TD>Accommodation and Food Svcs</TD><TD>27282.93</TD></TR><TR><TD>01JAN2013</TD><TD>Accommodation and Food Svcs</TD><TD>27938.16</TD></TR><TR><TD>01JAN2014</TD><TD>Accommodation and Food Svcs</TD><TD>28593.38</TD></TR><TR><TD>01JAN2015</TD><TD>Accommodation and Food Svcs</TD><TD>29248.6</TD></TR><TR><TD>01JAN2016</TD><TD>Accommodation and Food Svcs</TD><TD>29903.83</TD></TR><TR><TD>01JAN2017</TD><TD>Accommodation and Food Svcs</TD><TD>30559.05</TD></TR><TR><TD>01JAN2018</TD><TD>Accommodation and Food Svcs</TD><TD>31869.5</TD></TR><TR><TD>01JAN1990</TD><TD>Advanced Materials</TD><TD>7477.417</TD></TR><TR><TD>01JAN1991</TD><TD>Advanced Materials</TD><TD>7334.721</TD></TR><TR><TD>01JAN1992</TD><TD>Advanced Materials</TD><TD>7192.026</TD></TR><TR><TD>01JAN1993</TD><TD>Advanced Materials</TD><TD>7049.33</TD></TR><TR><TD>01JAN1994</TD><TD>Advanced Materials</TD><TD>6906.635</TD></TR><TR><TD>01JAN1995</TD><TD>Advanced Materials</TD><TD>6763.94</TD></TR><TR><TD>01JAN1996</TD><TD>Advanced Materials</TD><TD>6621.244</TD></TR><TR><TD>01JAN1997</TD><TD>Advanced Materials</TD><TD>6478.549</TD></TR><TR><TD>01JAN1998</TD><TD>Advanced Materials</TD><TD>6335.853</TD></TR><TR><TD>01JAN1999</TD><TD>Advanced Materials</TD><TD>6193.158</TD></TR><TR><TD>01JAN2000</TD><TD>Advanced Materials</TD><TD>6050.463</TD></TR><TR><TD>01JAN2001</TD><TD>Advanced Materials</TD><TD>5907.767</TD></TR><TR><TD>01JAN2002</TD><TD>Advanced Materials</TD><TD>5765.072</TD></TR><TR><TD>01JAN2003</TD><TD>Advanced Materials</TD><TD>5622.376</TD></TR><TR><TD>01JAN2004</TD><TD>Advanced Materials</TD><TD>5479.681</TD></TR><TR><TD>01JAN2005</TD><TD>Advanced Materials</TD><TD>5336.986</TD></TR><TR><TD>01JAN2006</TD><TD>Advanced Materials</TD><TD>5194.29</TD></TR><TR><TD>01JAN2007</TD><TD>Advanced Materials</TD><TD>5051.595</TD></TR><TR><TD>01JAN2008</TD><TD>Advanced Materials</TD><TD>4908.899</TD></TR><TR><TD>01JAN2009</TD><TD>Advanced Materials</TD><TD>4766.204</TD></TR><TR><TD>01JAN2010</TD><TD>Advanced Materials</TD><TD>4623.509</TD></TR><TR><TD>01JAN2011</TD><TD>Advanced Materials</TD><TD>4480.813</TD></TR><TR><TD>01JAN2012</TD><TD>Advanced Materials</TD><TD>4338.118</TD></TR><TR><TD>01JAN2013</TD><TD>Advanced Materials</TD><TD>4195.422</TD></TR><TR><TD>01JAN2014</TD><TD>Advanced Materials</TD><TD>4052.727</TD></TR><TR><TD>01JAN2015</TD><TD>Advanced Materials</TD><TD>3910.032</TD></TR><TR><TD>01JAN2016</TD><TD>Advanced Materials</TD><TD>3767.336</TD></TR><TR><TD>01JAN2017</TD><TD>Advanced Materials</TD><TD>3624.641</TD></TR><TR><TD>01JAN2018</TD><TD>Advanced Materials</TD><TD>3339.25</TD></TR><TR><TD>01JAN1990</TD><TD>Agribusiness</TD><TD>5567.5</TD></TR><TR><TD>01JAN1991</TD><TD>Agribusiness</TD><TD>5500.328</TD></TR><TR><TD>01JAN1992</TD><TD>Agribusiness</TD><TD>5433.155</TD></TR><TR><TD>01JAN1993</TD><TD>Agribusiness</TD><TD>5365.983</TD></TR><TR><TD>01JAN1994</TD><TD>Agribusiness</TD><TD>5298.81</TD></TR><TR><TD>01JAN1995</TD><TD>Agribusiness</TD><TD>5231.638</TD></TR><TR><TD>01JAN1996</TD><TD>Agribusiness</TD><TD>5164.466</TD></TR><TR><TD>01JAN1997</TD><TD>Agribusiness</TD><TD>5097.293</TD></TR><TR><TD>01JAN1998</TD><TD>Agribusiness</TD><TD>5030.121</TD></TR><TR><TD>01JAN1999</TD><TD>Agribusiness</TD><TD>4962.948</TD></TR><TR><TD>01JAN2000</TD><TD>Agribusiness</TD><TD>4895.776</TD></TR><TR><TD>01JAN2001</TD><TD>Agribusiness</TD><TD>4828.603</TD></TR><TR><TD>01JAN2002</TD><TD>Agribusiness</TD><TD>4761.431</TD></TR><TR><TD>01JAN2003</TD><TD>Agribusiness</TD><TD>4694.259</TD></TR><TR><TD>01JAN2004</TD><TD>Agribusiness</TD><TD>4627.086</TD></TR><TR><TD>01JAN2005</TD><TD>Agribusiness</TD><TD>4559.914</TD></TR><TR><TD>01JAN2006</TD><TD>Agribusiness</TD><TD>4492.741</TD></TR><TR><TD>01JAN2007</TD><TD>Agribusiness</TD><TD>4425.569</TD></TR><TR><TD>01JAN2008</TD><TD>Agribusiness</TD><TD>4358.397</TD></TR><TR><TD>01JAN2009</TD><TD>Agribusiness</TD><TD>4291.224</TD></TR><TR><TD>01JAN2010</TD><TD>Agribusiness</TD><TD>4224.052</TD></TR><TR><TD>01JAN2011</TD><TD>Agribusiness</TD><TD>4156.879</TD></TR><TR><TD>01JAN2012</TD><TD>Agribusiness</TD><TD>4089.707</TD></TR><TR><TD>01JAN2013</TD><TD>Agribusiness</TD><TD>4022.534</TD></TR><TR><TD>01JAN2014</TD><TD>Agribusiness</TD><TD>3955.362</TD></TR><TR><TD>01JAN2015</TD><TD>Agribusiness</TD><TD>3888.19</TD></TR><TR><TD>01JAN2016</TD><TD>Agribusiness</TD><TD>3821.017</TD></TR><TR><TD>01JAN2017</TD><TD>Agribusiness</TD><TD>3753.845</TD></TR><TR><TD>01JAN2018</TD><TD>Agribusiness</TD><TD>3619.5</TD></TR></TBODY></TABLE> Fri, 18 Oct 2019 19:19:36 GMT https://communities.sas.com/t5/SAS-Programming/How-to-interpolate-data-set-with-by-statement/m-p/596141#M171604 KRA15 2019-10-18T19:19:36Z Re: Animated Bubble Chart with years interpolated https://communities.sas.com/t5/SAS-Programming/How-to-interpolate-data-set-with-by-statement/m-p/596403#M171713 <P>1. Try reducing your problem into something more concise</P> <P>2. Provide the data as a data step</P> <P>3. Avoid attaching MS Office files</P> Tue, 15 Oct 2019 01:29:22 GMT https://communities.sas.com/t5/SAS-Programming/How-to-interpolate-data-set-with-by-statement/m-p/596403#M171713 ChrisNZ 2019-10-15T01:29:22Z