BookmarkSubscribeRSS Feed
PrinyaEag
Calcite | Level 5

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

 

 

1 REPLY 1
PGStats
Opal | Level 21

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;

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 638 views
  • 0 likes
  • 2 in conversation