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

PrinyaEag — Thu, 10 Aug 2017 19:20:44 GMT

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,

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

PGStats — Thu, 10 Aug 2017 20:10:59 GMT

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;

topic Re: How do I sum variable with an if condition using proc sql in SAS Procedures

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

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