topic Re: Bring in variable from other data set in SAS Studio

Bring in variable from other data set

sastuck — Sun, 15 Apr 2018 00:12:07 GMT

Hello SAS community,

@Shmuel offered me the following code so that I could bring a variable into the data set I am running a regression on from another data set in my program:

*bring variable in from other data set; 
proc sql;
     create table paper.ceo_firm as 
      select  a.* , b.VOL
      from paper.ceo_firm as a
      left join paper.CSRP_Monthly_Stock_char4 as b
     on a.ticker = b.ticker and
          a.year = b.year;
quit;

I ran into an error and @Shmuel identified it, saying that:

"The difference in number of observations means that the combination of ticker and year are not unique.

SQL join makes a Cartesian join thus multiplies output.

If you know what other variables make it unique - add them to the conditions"

But I had trouble implementing this because, for one reason, I'm not sure how it would look in the code, and, secondly, I can't imagine how else to make this variable unique since basically my whole program has been revolving around the ticker*year combination.

Let me know if you can help take my data set to the next level! If this works to call VOL into my ceo_firm dataset, then I could use it for other variables as well, which could be very useful to me.

Thanks for your time!

-SAStuck

Re: Bring in variable from other data set

SASKiwi — Sun, 15 Apr 2018 01:17:37 GMT

I think it would help to explain why both tables have duplicates by ticker plus year combination. At a guess your monthly stock table has monthly rows for each ticker/year. However why does your CEO firm table have multiple rows per ticker/year?

Re: Bring in variable from other data set

sastuck — Sun, 15 Apr 2018 01:39:03 GMT

Would it help the situation if I drop rows with multiple ticker/years?

Re: Bring in variable from other data set

SASKiwi — Sun, 15 Apr 2018 02:58:05 GMT

Posting a few rows showing the duplicates would help us understand the data better otherwise we are just guessing what the best approach should be.

Re: Bring in variable from other data set

sastuck — Sun, 15 Apr 2018 03:19:23 GMT

I ran the following code:

*find duplicates: ticker/year combo;
proc freq data=paper.CSRP_Monthly_Stock_char4;
	tables ticker * year / noprint 
		out=paper.multiple_tyCSRP (where=(count > 1));
run;

proc freq data=paper.ceo_firm;
	tables ticker * year / noprint 
		out=paper.multiple_tyfirm (where=(count > 1));
run;

And below I am posting examples of the output from the respective tables:

Row

Ticker

Year

Count

Percent

2011

0.00198938

2012

0.00198938

2013

0.00198938

2014

0.00198938

2015

0.00198938

2016

0.00198938

2017

0.00198938

2010

0.00198938

2011

0.00198938

2012

0.00198938

2013

0.00198938

2014

0.00198938

2015

0.00198938

2016

0.0018235984

2017

0.00198938

AAAP

2016

0.00198938

AAAP

2017

0.00198938

AABA

2017

0.00099469

That was paper.CSRP_Monthly_Stock_char4.

paper.ceo_firm is empty (no duplicates).

Re: Bring in variable from other data set

SASKiwi — Sun, 15 Apr 2018 04:15:01 GMT

So what do you want as an output dataset? If it needs to be unique by ticker / year, then what do you want VOL to be? The sum over each year, the average, the minimum or something else?

If you want the sum of vol, something like this should work:

proc sql;
     create table ceo_firm as 
      select  a.* , b.VOL
      from paper.ceo_firm as a
      left join
     (select ticker
                ,year
                ,sum(VOL) as VOL
      from paper.CSRP_Monthly_Stock_char4
      group by ticker
              ,year
     ) as b
     on a.ticker = b.ticker and
     a.year = b.year;
quit;

Re: Bring in variable from other data set

sastuck — Sun, 15 Apr 2018 13:26:06 GMT

Your code worked. I will accept it as the solution. I did run into an error though. I know I am just making a simple mistake:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 70         
 71         proc reg data=paper.ceo_firm plots(maxpoints=none);
 72         model salary=annual_return bonus stock_awards age male vol;
 ERROR: Variable VOL not found.
 NOTE: The previous statement has been deleted.
 73         run;
 
 WARNING: No variables specified for an SSCP matrix. Execution terminating.
 NOTE: PROCEDURE REG used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              3408.59k
       OS Memory           33216.00k
       Timestamp           04/15/2018 01:24:17 PM
       Step Count                        89  Switch Count  0
       Page Faults                       0
       Page Reclaims                     481
       Page Swaps                        0
       Voluntary Context Switches        3
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           48
       
 
 74         
 75         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 87

Should I adapt your code to name the table paper.ceo_firm? Or adapt my model to pull from the table your code created, work.ceo_firm? Let me know! Thanks!

Re: Bring in variable from other data set

SASKiwi — Sun, 15 Apr 2018 20:43:23 GMT

I deliberately created a new dataset rather than overwrite the existing one as it is not good practice to do so and you get a warning in your SAS log. I suggest you go with the new name. If you want a permanent dataset try something like paper.ceo_firm2.