How do I sum variable with an if condition using proc sql

[PrinyaEag]

Hello all,

I have a panel data that looks something like this:

Fund     Year     Stock     Sharesowned     Totaloutstanding     State  

A           2000    Apple      50,000                  10,000,000           CA          

A           2000    Google    100,000                15,000,000           CA          

A           2000    Exxon       35,000                  2,000,000             TX         

A           2000    Citi           8,000                    20,000,000             .            

B           2000    Pepsi       30,000                  6,000,000              NC         

B           2000    Shell        15,000                  8,000,000              TX        

B           2000    Apple       1,000                   15,000,000              CA         

What I need to do is create a variable that sums up all the 'Sharesowned' and 'Totaloutstanding' that is not within the state for each observation for each 'fund' 'year' and also just those that are not missing 'state'. I know proc sql can be used for this but I just can't figure out how to sum up just those observations that are outside the observation state within each fund year. Given my example above, my expected output is:

Fund     Year     Stock     Sharesowned     Totaloutstanding     State     Sumshares        Sumoustanding

A           2000    Apple      50,000                  10,000,000           CA          35,000               2,000,000

A           2000    Google    100,000                15,000,000           CA          35,000               2,000,000

A           2000    Exxon       35,000                  2,000,000             TX           150,000            25,000,000        

A           2000    Citibank    8,000                   20,000,000             .                   .                         .

B           2000    Pepsi       30,000                  6,000,000              NC          16,000              23,000,000

B           2000    Shell        15,000                  8,000,000              TX           31,000              21,000,000

B           2000    Apple       1,000                   15,000,000              CA         45,000               14,000,000

So looking at Fund A in Year 2000, we can see that they own 4 stocks, 2 in CA, 1 in TX, and 1 is missing an observation. The first observation is Apple which is in State CA. I need to sum up all of the holdings of Fund A in 2000 that is not within the state of california and is also not blank. In this case, the only one that would be added is Exxon which is in TX because the Citibank observation as a blank 'State'. Hence the 35,000 and 2,000,000 in the 'sumshares' and 'sumoustanding'

Thank you in advanced for the help,

AR

[PGStats]

You can use subqueries (not shown) or a left join:

proc sql;
select 
    a.*,
    sum(b.sharesowned) as sumshares,
    sum(b.totaloutstanding) as sumoutstanding
from 
    have as a left join
    have as b 
        on  a.fund=b.fund and 
            a.year=b.year and 
            a.state ne b.state and
            a.state is not missing
group by a.fund, a.year, a.stock, a.state, a.sharesowned, a.totaloutstanding;
quit;