CCM Linktable for merging CRSP and Compustat data: transform long to %tm format (Stata newbie)

[Elena Moschin]

I want to create a linktable for CRSP and Compustat data for GVkeys and Permno firm identifiers and have some problems with the different fiscal year formats the two databases (monthly vs yearly) use.

I've come so far as to input the Compustat fiscal year definition for the month and year of each Permno link start date (linkdt), resulting in one variable for month and one for year in the long format. But I don't know how to transform that now into a single variable in %tm format.

Any help?

Here is my code so far:

use "F:\...\20221215 CMM_GVKEY PERMNO links.dta"

rename LPERMNO permno

rename LPERMCO permco

drop gvkey_old

sort gvkey

rename LINKDT linkdt

rename LINKENDDT linkenddt

generate linkdt_month = month(linkdt)

replace linkdt_month = month(linkdt)-1 if day(linkdt)<15

generate linkdt_year = year(linkdt)

replace linkdt_year = year(linkdt)-1 if linkdt_month==0

replace linkdt_month = 12 if linkdt_month==0

[Andrew Musau]

Show the result of

dataex  linkdt_* in 1/10

[Devra Golbe]

Hi Elena,

Welcome to Statalist! A couple of hints to help you get answers to your questions. First, remember that most Forum members are unlikely to be familiar with your data (including CRSP Compustat and CCM.) Show us what a relevant sample of the data looks like by using the dataex command. More generally, the forum FAQs will be extremely helpful in formulating a clear question.

I do have some familiarity with these databases, and I don't understand your question. What are you trying to accomplish? Every observation in CRSP has a calendar date associated with it. Every Compustat observation indicates the end of the fiscal year in a variable called datadate. The link dates are likewise specific calendar dates. Why are you trying to work with year/month dates?

[Elena Moschin]

Hello you two,
thank you very much for that already! I hope I did the right thing with the data example.

I am trying to accomplish a merge for data from CRSP and Compustat for yearly variables using firm identifiers. I have a rough guide on what to do, but am very open for alternatives since I cannot get it working. Originally, I wanted to use the "CRSP/Compustat Merged Database - Linking Table" database to create a linktable to match up the different idenfiers to then be able to merge data from both databases.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 gvkey str2 LINKPRIM str4 LIID str2 LINKTYPE double(permno permco) long(linkdt linkenddt) float(linkdt_month linkdt_year)
"001327" "P" "01"  "LU" 45911 20058  2994    .e  2 1968
"001573" "P" "01"  "LC" 44652 20101  2735 14418  6 1967
"001602" "P" "01"  "LU" 14008   216  8568    .e  6 1983
"001686" "P" "01"  "LU" 14526   317  4779    .e  1 1973
"002111" "P" "01"  "LU" 39642 20284  1363    .e  9 1963
"002270" "P" "01"  "LC" 19661 53296 18254 19134 12 2009
"002270" "P" "03"  "LC" 21257 58272 22403    .e  4 2021
"002270" "P" "01"  "LC" 62519   559  7363 18253  2 1980
"002270" "P" "01"  "LC" 93159 53296 19135 22144  5 2012
"002554" "P" "01"  "LU" 20240  6609  8726 14230 11 1983
"002751" "P" "01"  "LC" 21371  6262  8616    .e  7 1983
"003243" "P" "01"  "LC" 70519 20483  9798    .e 10 1986
"003265" "J" "02"  "LU" 10195  1221  9646 15978  5 1986
"003265" "P" "01"  "LC" 25348  1221  4731 17255 11 1972
"003905" "C" "01"  "LU" 76708 10992 11494    .e  6 1991
"004476" "P" "01"  "LC" 87696  7670  9344 20950  7 1985
"004800" "P" "01"  "LC" 22496 20739   761 14944  1 1962
"004807" "P" "01"  "LU" 37058  5848  8469 19782  2 1983
"004881" "P" "01"  "LU" 37568  1688  4731    .e 11 1972
"004961" "P" "01"  "LU" 21004 20776   761 14822  1 1962
"004961" "C" "00X" "LU" 21004 20776 -3652   760 12 1949
"005331" "P" "01"  "LC" 67571  5955  8524 14060  4 1983
"005539" "P" "01"  "LU" 41292  6616  8727    .e 11 1983
"005709" "P" "01"  "LU" 32870 20932   913    .e  6 1962
"005763" "P" "02"  "LU" 86313 20867 15887    .e  6 2003
"005763" "J" "02"  "LU" 86313 20867 14108 15886  8 1998
"005763" "J" "01"  "LU" 50585 20867 15887    .e  6 2003
"005763" "P" "01"  "LU" 50585 20867  3790 15886  5 1970
"005860" "P" "01"  "LC" 12570 20964   761    .e  1 1962
"005860" "C" "00X" "LC" 12570 20964 -3652   760 12 1949
"005885" "P" "01"  "LU" 43610  6125  8582 15552  6 1983
"006178" "P" "01"  "LC" 21370 21004   761 15061  1 1962
"006268" "P" "01"  "LC" 42534 21019  2110 20699  9 1965
"006268" "P" "01"  "LC" 45356 21791 20700    .e  8 2016
"006507" "P" "01"  "LU" 59897  2587  4731 14334 11 1972
"007182" "P" "01"  "LU" 59942  3058  4731 13604 11 1972
"007250" "P" "01"  "LC" 52792  2983  6459 17920  8 1977
"007257" "P" "01"  "LC" 22752 21188   732    .e 12 1961
"007257" "C" "00X" "LC" 22752 21188 -3652   731 12 1949
"007346" "P" "01"  "LC" 53640  3047  7756 21333  3 1981
"007448" "P" "01"  "LU" 80829 27394 12649 15743  8 1994
"007475" "P" "01"  "LC" 15966 21211   761 14578  1 1962
"007475" "C" "00X" "LC" 15966 21211 -3652   760 12 1949
"007562" "P" "01"  "LC" 48071 21222  3378 14973  3 1969
"007637" "P" "01"  "LC" 69550  3104  4802 22235  2 1973
"007637" "P" "01"  "LC" 20057 57529 22236    .e 11 2020
"007750" "P" "01"  "LC" 52337 21258  4135    .e  4 1971
"007922" "P" "01"  "LU" 57817  3217  4731    .e 11 1972
"008092" "P" "01"  "LU" 59089  3370  4731 21853 11 1972
"008092" "P" "03"  "LC" 13743 54299 22078    .e  5 2020
"008386" "P" "01"  "LU" 61496  3466  6605    .e  1 1978
"008386" "C" "00X" "LU" 61496  3466  5204  6604  3 1974
"008468" "P" "01"  "LU" 35211 21381   913 14487  6 1962
"008675" "C" "01"  "LC" 56143 21427  4736 17399 12 1972
"008794" "P" "03"  "LC" 64531  3430 13149 14425 12 1995
"008794" "J" "03"  "LC" 64531  3430 13144 13148 12 1995
"008794" "P" "01"  "LC" 64531  3430  4731 13143 11 1972
"008873" "P" "01"  "LC" 27756 21476   761 20713  1 1962
"008873" "C" "00X" "LC" 27756 21476   731   760 12 1961
"009284" "P" "01"  "LC" 54818 21537  4527 19751  5 1972
"009483" "P" "01"  "LU" 45671 21580  2951 16860  1 1968
"009774" "C" "01"  "LC" 70923  4062  4731 19627 11 1972
"010033" "C" "00X" "LU" 72710  7719  4808  7699  2 1973
"010033" "P" "01"  "LU" 72710  7719  7700 15946  1 1981
"010063" "P" "01"  "LC" 27254 21670  6419 19662  7 1977
"010063" "C" "00X" "LC" 27254 21670  6391  6418  6 1977
"010550" "C" "01"  "LU" 76100  4587  7804 20391  4 1981
"010580" "P" "01"  "LC" 81193 30844 12815 14773  1 1995
"010580" "P" "01"  "LC" 40635 21757  1608 12814  5 1964
"010793" "P" "01"  "LC" 77730  4620  4731    .e 11 1972
"010839" "P" "01"  "LC" 78044  4685  4739    .e 12 1972
"010857" "P" "01"  "LU" 15659 21806   761 15012  1 1962
"010857" "C" "00X" "LU" 15659 21806 -3652   760 12 1949
"010974" "P" "01"  "LC" 15077 21796   761 17902  1 1962
"010974" "C" "00X" "LC" 15077 21796 -3652   760 12 1949
"011038" "P" "01"  "LC" 14891 21842   761 16658  1 1962
"011038" "C" "00X" "LC" 14891 21842 -3652   760 12 1949
"011312" "P" "01"  "LU" 46420 21892  3101 14669  6 1968
"012151" "P" "01"  "LC" 13983 54444 19522    .e  5 2013
"012380" "P" "01"  "LU" 10185  8128  9646 14578  5 1986
"012576" "C" "02"  "LC" 10659  8563  9751 20319  8 1986
"012583" "P" "01"  "LU" 10738  8634  9777 13909  9 1986
"012669" "C" "01"  "LC" 89386  8646 20789 20818 11 2016
"012669" "P" "01"  "LC" 89386  8646 15483 20788  5 2002
"012669" "P" "01"  "LC" 10750  8646  9779 14986  9 1986
"012788" "P" "01"  "LU" 75224  8741  9814 20909 10 1986
"013365" "P" "01"  "LC" 11415  9252 10024 17888  5 1987
"013407" "P" "01"  "LC" 11384  9227 10015 17507  5 1987
"013474" "P" "01"  "LU" 11411  9251 10023 17164  5 1987
"013779" "P" "01"  "LU" 10451  8367  9679 13573  6 1986
"013866" "P" "01"  "LU" 75172 21014 10084 16770  7 1987
"014129" "P" "01"  "LC" 75162 20732 10126 17959  9 1987
"014363" "P" "01"  "LC" 75259 20957 10252 16373  1 1988
"014370" "P" "01"  "LC" 75285 20312 11445    .e  4 1991
"014370" "P" "01"  "LC" 19581 57197 10350 11444  4 1988
"016729" "P" "01"  "LC" 75834 10335 10884 15785 10 1989
"016769" "P" "01"  "LC" 23326   991  8064 17531  1 1982
"016769" "C" "00X" "LC" 23326   991  8036  8063 12 1981
"017110" "P" "01"  "LU" 75857 10371 10911 17470 11 1989
"017254" "P" "01"  "LC" 10085  8029  9561 16919  2 1986
end
format %td linkdt
format %td linkenddt

[Andrew Musau]

I cannot advise on a strategy here, but on your original question in #1

gen wanted= ym(linkdt_year,linkdt_month )
format wanted %tm

See

help ym()

[Elena Moschin]

Ok, thank you so much! That worked! Then I'll try to continue with my original rough plan for merging the databases via a linktable.