Hi, I am dealing with unique data (portfolio holdings) and I want to calculate the change in holdings. My question arises when there was holdings (a particular stock i of portfolio p) in t-1 period but no holdings of the stock at t (current quarter). Data set looks like (For given year and quarter, portfolio (p1) is composed of the stocks si): Portfolios   Year   Qtr   Stock    $amount of stock held by pi ($holdings)        p1         2000    1      s1            100     p1         2000    1      s2            200     p1         2000    1      s3             50     p1         2000    1      s4            100     p1         2000    2      s1            150     p1         2000    2      s3            100     p1         2000    2      s4            100     p1         2000    3      s1            200     p1         2000    3      s3            100     p1         2000    3      s4            150     p1         2000    3      s5            100 Then, after considering change in holdings, the data structure I am looking for is Portfolios   Year   Qtr   Stock    $amount of stock held by pi    change in holdings        p1         2000    2     s1                 150                               50  (=150-100)     p1         2000    2     s2                  0                               -200  (=0-200 :sold)     p1         2000    2     s3                 100                               50  (=100-50)     p1         2000    2     s4                 100                                 0  (=100-100)     p1         2000    3     s1                 200                                50  (=200-150)     p1         2000    3     s3                 100                                0  (=100-100)     p1         2000    3     s4                 150                                50  (=150-100)     p1         2000    3     s5                 100                              100  (=100-. :new ob) The key concern is on how to capture the change of stocks that are completely sold from the holdings (in this case s2 in 2nd quarter). Your help will be greatly appreciated! Thanks, Markov               ... View more

Dear experts, I have tried to merge some variables with numerical values from one table(table b) to another table(table a) using SQL left join (on a.year=b.year and a.codeA=b.codeA and a.codeB=b.codeB). What it returns me is the table with the variables what I wanted from table b (let's say INCOME AGE etc) but no numerical values from table b. I see no error message in log. What could be the reason for this results? Best, MB ------------------------------- Thanks for your replies, experts. I have attached the data sets (table a and table b) as well as result table where I am having trouble mentioned in the original discussion). SAS Code I used is as the following; proc sql; create table sample_result as select a.*, b.edu, b.mhincome, b.mage from sample_a as a left join sample_b as b on a.year=b.year and a.state_code=b.state_code and a.co_code=b.co_code; quit; Thank you for your help! MB ... View more

Dear who can help this out; I have state level socioeconomic data set from census, income, race ratio, marriage ratio, education level etc. It's in each 10 year period (1980, 1990, 2000). I want to linearly interpolate each variable values for the years in between (values for 1981~1989, 1991~1999). Data structure now looks like this State     year   income race ratio marriage ratio education level Alabama  1980 10000     0.40          0.52              0.34 Alabama  1990 12000     0.44          0.50              0.32 Alabama  2000 13000     0.48          0.58              0.39 California 1980 15000     0.20          0.63              0.42 California 1990 16000     0.25          0.66              0.43 California 2000 16500     0.30          0.69              0.47 Any idea on this particular procedure with this set of data would be appreciated! Thank you, MB ... View more