topic Re: merging compustat global annual data and compusat global security daily data in SAS Programming

merging compustat global annual data and compusat global security daily data

Theo_Gh — Sun, 12 Feb 2017 05:56:05 GMT

Hello everyone,

I'm trying to merge compustat global annual data with compustat global security data by gvkey. The codes I have used so far don't work.

Your assistance would be very much appreciated.

Thanks

Re: merging compustat global annual data and compusat global security daily data

Kurt_Bremser — Sun, 12 Feb 2017 07:30:08 GMT

Did the suggestions you got in https://communities.sas.com/t5/General-SAS-Programming/Data-Step-Merge-variable-declared-as-both-character-and-numeric/m-p/331776#M43373 not bring you on the right track?

Please post the code you tried, the log, how the result differed from your expectations, and some example data (if data is retrieved from the internet, post the URL).

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Sun, 12 Feb 2017 08:04:51 GMT

As I indicated, I used the wrong identifier so I did check my data and did the right thing thanks to you guys.

On this issue, I have actually succeeded in merging them but I'm not sure if it is correct. I used these codes:

proc sort data=data1;

by gvkey;

run;

proc sort data=data2;

by gvkey;

run;

data data3;

merge data1(in=a) data2(in=b);

by gvkey;

if b;

run;

Thanks

Re: merging compustat global annual data and compusat global security daily data

Kurt_Bremser — Sun, 12 Feb 2017 09:13:18 GMT

First of all, take a look at the log. There should be no WARNINGs or ERRORs. The NOTEs should only be of the nature "xxx records read from ..." and "the dataset .... has xxxx records and yyy variables".

Crucial for a datastep merge is the relationship between the datasets. It should be 1:1 or 1:n, m:n are probably handled better with SQL.

So if you have any doubts, post the log.

Re: merging compustat global annual data and compusat global security daily data

Shmuel — Sun, 12 Feb 2017 09:16:07 GMT

If both data1 and data2 have same gvkey more than once ( relation n:m where n>1 and m>1)

you may have issues with the merge.

If in any of the two datas each gvkey occures once only it will work perfectly.

In Case of N:M relation better do:

proc sql;
     create table want as select *
     from data1 as a
     join data2 as b
     on a.gvkey = b.gvkey;
quit;

In case not all keys in data1 exsist in data2 or vice-versa then you may need

use inner join or full join.

See next PDF documentation:

https://www.google.co.il/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0ahUKEwiMkqTum4rSAhWIWxQKHWsdBlIQFgghMAA&url=http%3A%2F%2Fsupport.sas.com%2Fresources%2Fpapers%2Fproceedings12%2F251-2012.pdf&usg=AFQjCNGX8KcGrMc9fSQ3s55p0Rnto60jnw&sig2=XBymq26ZUt-F2389C0J9dQ&bvm=bv.146786187,d.bGs&cad=rja

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Sun, 12 Feb 2017 11:01:56 GMT

Thank you very much.

I'm posting the log here; i got no error per se. Please ignore the data set definitions.

My next challenge is to calculate some variables at the end of the year fiscal year. My question is how to set the date right.

Thank you.

13 proc sort data=theo.to_be_merged;

14 by gvkey;

15 run;

NOTE: Input data set is already sorted, no sorting done.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

16 proc sort data=theo.security_daily;

17 by gvkey;

18 run;

NOTE: Input data set is already sorted, no sorting done.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

19 data data3;

20 merge theo.to_be_merged(in=a) theo.security_daily(in=b);

21 by gvkey;

22 if b;

23 run;

NOTE: MERGE statement has more than one data set with repeats of BY values.

NOTE: There were 2357018 observations read from the data set THEO.TO_BE_MERGED.

NOTE: There were 86856448 observations read from the data set THEO.SECURITY_DAILY.

NOTE: The data set WORK.DATA3 has 86857588 observations and 38 variables.

NOTE: DATA statement used (Total process time):

real time 7:48.17

cpu time 53.68 seconds

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Sun, 12 Feb 2017 11:02:46 GMT

Thank you.

Re: merging compustat global annual data and compusat global security daily data

mkeintz — Sun, 12 Feb 2017 12:35:19 GMT

You are merging annual with daily data. Given that it's Compustat, both dataset probably have identifiers gvkey and datadate, where datadate is the last date of the fiscal year for annual data,, and day of trading for the security data.

Question: do you want to merge the security data with the immediately PRECEDING annual data, or the FOLLOWING annual data?

And also, are you going to allow for the fact that accounting data is not usually reported until about 45 days after the fiscal year?

Re: merging compustat global annual data and compusat global security daily data

Reeza — Sun, 12 Feb 2017 14:10:57 GMT

I don't know if it's correct but I would hesitate to say it was.

Look at your last step and notice how your final results have more than either of your original tables. You need to figure out why this occurs, where and if it makes sense given your data.

Re: merging compustat global annual data and compusat global security daily data

mkeintz — Sun, 12 Feb 2017 16:17:10 GMT

Why is there only about 43 daily records per annual record? There are typiically 200 trading days per year. Do you have instance of annual accounting data for GVKEY's that do no appear in the daily security data file? If so, what do you want to do about it?

Your program,merging by gvkey, will not produce what you want, unless (for those gvkey's in both datasets) you have precisely the same series of datadates in both datasets.

Here is a program that outputs every daily record (from dataset SEC) merged with the closest following annual record (dataset ANN). Both files are sorted by gvkey datadate.

data want;
  do until (ina1 or last.gvkey);
    set sec ann (in=ina1);
    by gvkey datadate;
  end;

  do until (ina2 or last.gvkey);
    set sec (in=ins) ann (in=ina2 keep=gvkey datadate);
    by gvkey datadate;
    if ins=1 and ina1=1 then output;
  end;
run;

Notes:

The first "do until" reads all the records until an annual record.This gets the annual record variables into the program data vector(pdv).
The second "do until" rereads all the same records.
1. Note there is a "keep=gvkey datadate" for the ANN dataset. Otherwise all the other annual vars read in by the first do group would be reset to missing with every incoming record, whether from SEC or ANN. And since SEC doesn't have those variables they would stay missing. The "keep=" parameter tells SAS not to revise ANN vars not in the keep list.
2. Use a new dummy var INA2, because dummy INA1 is still being used in this do group.
Only incoming records coming from the SEC dataset (INS=1), and for which there was a qualifying ANN record found in the first "do until" (INA1=1) are written out. This eliminates all cases of gvkey in SEC but not not having corresponding ANN records.
If you have, say, two years of SEC records but only one ANN record at the end of the two years, and if you want only the 2nd year of SEC data, then this modification is needed, that uses a date_cutoff of 1 year prior to the annual datadate:

data want;
  do until (ina1 or last.gvkey);
    set sec ann (in=ina1);
    by gvkey datadate;
  end;

  if ina1 then date_cutoff=intnx('year',datadate,-1,'same')+1;

  do until (ina2 or last.gvkey);
    set sec (in=ins) ann (in=ina2 keep=gvkey datadate);
    by gvkey datadate;
    if ins=1 and ina1=1 and datadate>=date_cutoff then output;
  end;
run;

Re: merging compustat global annual data and compusat global security daily data

Shmuel — Sun, 12 Feb 2017 20:35:47 GMT

@Theo_Gh, in your log there is a message:

19   data data3;
20       merge theo.to_be_merged(in=a) theo.security_daily(in=b);
21       by gvkey;
22       if b;
23   run;
 
NOTE: MERGE statement has more than one data set with repeats of BY values.

that means, your datasets are in relation N:M per gvkey.

Run next test code and compare results of merge vs sql -

data tst_nok vs tst_ok:

/* merge M:n vs SQL */
data tst1;
  key = 1; varn = 5; varx='A'; output;
  key = 1; varn = 6; varx='B'; output;
  key = 1; varn = 7; varx='C'; output;
run;

data tst2;
  key = 1; varm = 8; vary='X'; output;
  key = 1; varm = 9; vary='Y'; output;
run;

data tst_nok;
merge tst1 tst2;
  by key;
run;

proc sql;
  create table tst_ok as 
  select a.*, b.varm, b.vary
  from tst1 as a
  left join tst2 as b 
  on a.key = b.key
  order by key,varn;
quit;

I recommend use sql to join data from both datasets or

use @mkeintz code if it fits your needs.

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Mon, 13 Feb 2017 05:05:04 GMT

Thank you very much. Will try it.

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Mon, 13 Feb 2017 05:07:24 GMT

Thank you very much.I did run your codes and got this:
NOTE: There were 86856448 observations read from the data set THEO.COPY_SECURITY.
NOTE: There were 373633 observations read from the data set THEO.COPY_COMPUSTAT.
NOTE: There were 86856448 observations read from the data set THEO.COPY_SECURITY.
NOTE: There were 373633 observations read from the data set THEO.COPY_COMPUSTAT.
NOTE: The data set WORK.WANT has 78137460 observations and 45 variables.

I noticed that the annual data has been matched against the daily data so that's good. But I have to merge the 'want' data set with other annual data sets. Will a simple proc sort by fyear help?

Thank you once again for your time and help !

Re: merging compustat global annual data and compusat global security daily data

mkeintz — Mon, 13 Feb 2017 14:51:38 GMT

Your "want" dataset has 7.8M records, but the security dataset had 8.6M. That means there are .8M security records for which there were no matching annual records. I presume that is unsurprising, right?

What does your other annual data look like? Does it have gvkey? You mention fyear (fiscal year). Are you saying that your other annual data does not have datadate?

Why do you think that sorting by fyear is neccessary? If your current data is sorted by gvkey / datadate, is it not likely to also already be sorted by gvkey/fiscal year? You can test this by this simple step:

data _null_;

set want ;

by gvkey fyear;

run;

If SAS produces no "out of order" message on the log for this program, you have demonstrated that the data is already sorted.

Also maybe you should have merged all the annual records first (smaller N), then merge with the daily data.

PS: For latecomers to this topic, here's the link to the code that answered the topic question:

https://communities.sas.com/t5/General-SAS-Programming/merging-compustat-global-annual-data-and-compusat-global/m-p/331967#M43407

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Mon, 13 Feb 2017 13:00:01 GMT

Thank you very much for your time and patience. I don't know what I would done without this platform.

No, it's not surprising that the daily data is more than the annual data. From the information I have not all the firms are actively trading on the stock exchange.

Yes, I can merge the annual data before merging with the daily data. My question still is: take for instance that I want the market value as of the end of a particular year. If I still simply multiply share price by number of shares outstanding, I'm going to have different values since the daily stock prices have been matched against the annual shares outstanding. In this particular eg i want the share price at the end of the fiscal year times the closest annual number of shares outstanding (which should give me one value). I guess my question is : How do I need to do so that after matching annual and daily data sets I would get one value for an answer ( which should be the end of fiscal year value) and not multiple values, which should be the same nonetheless.

I count on your usual corporation

Re: merging compustat global annual data and compusat global security daily data

mkeintz — Mon, 13 Feb 2017 14:45:22 GMT

@Theo_Gh wrote:

Thank you very much for your time and patience. I don't know what I would done without this platform.

No, it's not surprising that the daily data is more than the annual data. From the information I have not all the firms are actively trading on the stock exchange.

I was asking about the LOSS of .8M SECURITY records, which (I presume) represents daily prices on public stock exchanges. They are ignored by the program because you have no annual data to match them. That's the opposite of your explanation (inactive stock trading). Since the annual data presumably comes from SEC filings extracted by Compustat, either (1) SEC does not have that company's filings (which I believe is not possible for publicly traded firms), or (2) Compustat did not retrieve those filings, or (3) your lost SEC records are recent, and the corresponding SEC filing has yet to be filed or processed by Compustat. My question was whether that indicated a possible research problem in the data your are using.

The annual Compustat data has the number of outstanding shares at the end of the fiscal year (CSHO - i.e. on the DATADATE of the annual record. Of course the problem is that there is no guarantee that shares outstanding were constant throughout the fiscal year.

However, the standard Compustat securities daily file has not only closing price variable (PRCCD - "Price Closing - Daily"), but also apparently daily common shares outstanding (CSHOC - "Common Shares Outstanding - Current"). Does your daily file have these variables? That will solve the problem of calculating marekt value.

BTW, please realize there can be more than one security stored under the same GVKEY (i.e. a company may have multiple classes of stocks on the markets). These are distinguished by the variable IID (Issue ID). You should make sure your securities file only has the IID of the standard common stock of the company.

Apologies to forum participants for getting into Compustat-specific issues so deeply, but the problems here have structural issues that can be generally informative.

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Mon, 13 Feb 2017 15:57:34 GMT

Thank you.
No, current file does not have them but I'm downloading them now (will take a while; internet is slow). I will do as you have advised and get back. Thank you.

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Tue, 14 Feb 2017 09:44:16 GMT

I have downloaded the daily security data with IID, PRCCD,CSHOC so I can calculate the daily market value of equity. However, the annual compustat data does not have iid. I'm interested in the market value of equity at the end of the fiscal year or the nearest. If I'm able to get these to match the annual compustat data, it should not present any serious research problem. No?

Any way, I tried using the datadata and SEDOL( The Stock Exchange Daily Official List(SEDOL) Code identifies issues) and got similar results as with gvkey.

Re: merging compustat global annual data and compusat global security daily data

Theo_Gh — Tue, 14 Feb 2017 11:03:54 GMT

When I use sql, I do not get " MERGE statement has more than one data set with repeats of BY values" . But will it work when one data set is annual and the other is daily ?

Thank you.

Re: merging compustat global annual data and compusat global security daily data

Kurt_Bremser — Tue, 14 Feb 2017 11:40:33 GMT

You do not get this message because SQL creates a cartesian product in many-to-many situations.

See this code

data have1;
input id value1;
cards;
1 1
1 2
;
run;

data have2;
input id value2;
cards;
1 3
1 4
1 5
;
run;

proc sql;
create table want as select
a.id, a.value1, b.value2
from have1 a inner join have2 b
on a.id = b.id
;
quit;

proc print data=want noobs;
run;

The result is

id    value1    value2

 1       1         3  
 1       2         3  
 1       1         4  
 1       2         4  
 1       1         5  
 1       2         5

Now a data step works differently:

data want2;
merge
  have1 (in=a)
  have2 (in=b)
;
by id;
if a and b;
run;

proc print data=want2 noobs;
run;

You get the NOTE: MERGE statement has more than one data set with repeats of BY values.
And this is the result:

id    value1    value2

 1       1         3  
 1       2         4  
 1       2         5