title: Calculated variable in a sub-query
body: <p>I've got a question for the PROC SQL experts. I have this code:</p>
  
  <pre><code>    proc sql;
      create table FinalData as 
      select * 
              ,Sum(starting_year,year_diff) as colsum
              ,Price*(1+(SELECT Return from OtherData where Year=calculated colsum)) as PriceFinal
     from MainData;
     quit;
  </code></pre>
  
  <p>Obviously the <code>calculated</code> keyword does not work, I think variables have to be on the same select for that. I want to be able to calculate and use <code>colsum</code> in the subquery all within one sql statement. I want to avoid recalculating <code>colsum</code> in every subquery because eventually i'll use more complex functions that could slow down the code if recalculated each time. </p>
  
  <p>I've found this <a href="https://stackoverflow.com/questions/16487587/use-calculated-values-from-subquery-in-another-variable">question</a> which seems to be pretty much the same, but I didn't manage to make the code work with that answer. </p>
  
  <p>EDIT: Changed the code a bit.</p>
  
  <p>It was actually meant to be <code>Year=calculated colsum</code>. OtherData is basically a reference table, where <code>Year</code> doesn't have duplicates. Here is an example:</p>
  
  <pre><code>           MainData                     OtherData
    [Price] [starting_year] [year_diff]         [Return] [Year]
      5.00         2010          5                 0.04    2015
      2.33         2013          3                 0.02    2016
      4.51         2011          1                 0.005   2017
                                                   0.1     2018
  </code></pre>
  
  <p>There will be missing values and thats fine. I know this can be easily done with multiple proc sql statements but the challenge is to do it in a single one. The <code>SUM</code> could be any other function that generates an output to be looked up in OtherData. </p>
  
  <p>Is there  a way to do this ? </p>