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'
You can use subqueries (not shown) or a left join:
sum(b.sharesowned) as sumshares,
sum(b.totaloutstanding) as sumoutstanding
have as a left join
have as b
on a.fund=b.fund 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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.