6 of 7
6
SEC Filings on EDGAR SAS File
Posted: 12 November 2014 01:33 PM   [ Ignore ]   [ # 76 ]
Newbie
Rank
Total Posts:  9
Joined  2014-01-11

Hi Joost,

Thanks again for your help. Yes, I’ve been using that link you posted for the code; thank you again for posting this. I’ve been struggling through this initial hurdle with SAS, and I’m not having much luck. Right now, I’m ok with using either of your “main scenarios”. At this point, I’m not sure I care what path I take as I still haven’t decided on a final sample (so I could download all 10-Ks using scenario 2 or figure out my sample first and follow scenario 1).

But for learning purposes, say I have a sample of 500 firms I want 10-Ks for. At this stage, I’m not sure how I “merge” the CRSP/COMPUSTAT file that lists the 500 firms with the edgar.filings table in SAS (assuming I take this path).

If I take your 2nd scenario then I am also stuck as I am not sure how I “filter” the filings to select the ones I want (or say I want ALL 10-Ks) to output the c_10K_list.txt file that PERL needs to read. (in code: OUTFILE= “C:\temp\c_10K_list.txt”)

All my confusion is coming from the SAS code at this point. From the code below, do I have to modify any of these first lines to tell SAS what 10-Ks I want outputted in the c_10K_list.txt file I’m trying to make SAS create?

Thanks again!
Mark


/* select 10-K filings from edgar.filings which holds all SEC filings */
proc sql;

  create table a_10k as
  select
      distinct cik, coname as edgarConame, filename as url,
      date as filingdate10K, formtype
  from
      edgar.filings b
  where
      formtype IN (“10-K”, “10-K/A”, “10-K405”, “10-K405/A”,
      “10-KSB”, “10-KSB/A”, “10-KT”, “10-KT/A”, “10KSB”,
      “10KSB/A”, “10KSB40”, “10KSB40/A”, “10KT405”,
      “10KT405/A”);

quit;

Profile
 
 
Posted: 12 November 2014 01:36 PM   [ Ignore ]   [ # 77 ]
Newbie
Rank
Total Posts:  9
Joined  2014-01-11

This might help as well. From a previous attempt at this I have a file called c_10K_list.txt on my harddrive. Some of the output is below (it goes on for 4000 lines/10-Ks or so). Where I am stuck is figuring out how I got SAS to output this particular list of 4000 10-Ks in the first place. I am trying to work through the SAS code you posted as I’m sure this is needed to get build the initial list (http://www.wrds.us/index.php/tutorial/view/26)

Sorry for the lengthy posts. Your help is greatly appreciated!
Mark

downloadId,url,blank
1,edgar/data/1800/0000912057-94-000771.txt,0
2,edgar/data/1800/0000912057-95-001314.txt,0
3,edgar/data/1800/0000912057-96-004299.txt,0
4,edgar/data/1800/0000912057-97-008391.txt,0
5,edgar/data/1800/0001047469-98-009108.txt,0
6,edgar/data/1800/0001047469-99-008952.txt,0
7,edgar/data/1800/0000912057-00-011377.txt,0
8,edgar/data/1800/0000912057-01-006039.txt,0
9,edgar/data/1800/0000912057-02-007043.txt,0
10,edgar/data/1800/0001047469-03-006107.txt,0
11,edgar/data/1800/0001047469-04-005498.txt,0
12,edgar/data/2488/0000891618-94-000068.txt,0
13,edgar/data/2488/0000898430-95-001399.txt,0
14,edgar/data/2488/0000898430-96-000902.txt,0
15,edgar/data/2488/0000898430-97-004123.txt,0
16,edgar/data/2488/0001012870-98-000595.txt,0
17,edgar/data/2488/0001012870-99-000892.txt,0
18,edgar/data/2488/0001012870-00-001494.txt,0
19,edgar/data/2488/0001012870-01-001228.txt,0
20,edgar/data/2488/0000898430-02-003290.txt,0
21,edgar/data/2488/0001012870-03-001181.txt,0
22,edgar/data/2488/0001193125-04-037179.txt,0


Also, in case this helps you understand where I’m going wrong: the first error I get when running the code (http://www.wrds.us/index.php/tutorial/view/26) in SAS is: “ERROR: Libref EDGAR is not assigned” (followed by other errors but I’m assuming they stem from this).

Profile
 
 
Posted: 12 November 2014 06:53 PM   [ Ignore ]   [ # 78 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Mark,

To select which filings to download, you need to decide if you (also) need the amended 10-Ks (10-K/A), small business 10-Ks (10-KSB), etc. If you only need 10-Ks, then you filter on 10-Ks only.

like:

proc sql;
     
create table a_10k as
    
select 
        distinct cik
coname as edgarConamefilename as url
        
date as filingdate10Kformtype
    from
        edgar
.filings b
    where
        formtype IN 
("10-K");
quit

If you already have a sample of firms (selected from Compustat/CRSP), then you need to match your sample with the Edgar filings table. The firm identifier (key) for Edgar is CIK (Central Index Key). The key for Compustat is GVKEY, for CRSP it is permno. The ‘current’ CIK is included in Compustat Fundamental Annual. That means you can find the filings for firms that have not changed their CIK over time. I am using the WRDS SEC Analytics Suite that has a linktable that includes some matches for historic CIKs (that makes the match between Compustat and Edgar somewhat better).

So, the workflow could look like something like this:
- select your sample of firms from Compustat
- match with CRSP for stock returns, etc
- match with IBES to get analyst forecasts
- use historic CIK in Funda as a key to match with Edgar and select 10-Ks to download
- retrieve 10Ks, process 10-Ks, generate csv file with scan results,
- import csv in SAS, match on ‘downloadId’
- ..

Hope this helps,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 13 November 2014 10:24 AM   [ Ignore ]   [ # 79 ]
Newbie
Rank
Total Posts:  9
Joined  2014-01-11

Hi Joost,

This does help - thank you, and sorry for the rudimentary question.

One quick follow up on the step: “- use historic CIK in Funda as a key to match with Edgar and select 10-Ks to download”...

In this step, do I insert these CIK numbers somewhere into the code below? For instance, say I just want to retrive the 10-K for QWEST CORP in 2012. The CIK is 68622 and the corresponding filename from the edgar.filings table is: edgar/data/68622/0001047469-12-002072.txt.

proc sql;
    create table a_10k as
  select
      distinct cik, coname as edgarConame, filename as url,
      date as filingdate10K, formtype
  from
      edgar.filings b
  where
      formtype IN (“10-K”);
quit; 

Thank you again,
Mark

Profile
 
 
Posted: 14 November 2014 01:27 PM   [ Ignore ]   [ # 80 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Mark,

Say you have a dataset called ‘dsin’ that has gvkey and cik, and that you want to retrieve the 10-Ks filed in 2000-2010 for these firms.

You could then do something like this:

proc sql;
    
create table a_10k as
  
select 
      distinct a
.coname as edgarConamea.filename as url
      
a.date as filingdate10Ka.formtypeb.gvkeyb.cik
  from
      edgar
.filings adsin b
  where
      a
formtype IN (“10-K”)  and 2000 <= year(a.date) <= 2010
      
and a.cik b.cik
;
quit

best regards,

Joost

 

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 10 March 2015 12:52 PM   [ Ignore ]   [ # 81 ]
Newbie
Rank
Total Posts:  4
Joined  2015-03-10

When I download the data from here http://www.wrds.us/index.php/repository/view/25 SAS tells me the file is not compatible with my version of SAS. I have SAS 9.2 64-bit. Any suggestions? What version of SAS is the data created for? thank you!

“ERROR: File EDGAR.FILINGS.DATA not compatible with this SAS version.”

Profile
 
 
Posted: 10 March 2015 01:21 PM   [ Ignore ]   [ # 82 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Allison,

How large is the downloaded file (zipped and unzipped?

I have SAS 9.4, but I would expect it to work for 9.2.

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 10 March 2015 01:23 PM   [ Ignore ]   [ # 83 ]
Newbie
Rank
Total Posts:  4
Joined  2015-03-10

The unzipped file is 1,914,176 KB, the zipped file is 209,157 KB

Profile
 
 
Posted: 10 March 2015 01:39 PM   [ Ignore ]   [ # 84 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Allison,

Ok, I have the same size. Do you have a way to convert SAS files to other formats (for example csv and then import it as csv)?

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 10 March 2015 02:01 PM   [ Ignore ]   [ # 85 ]
Newbie
Rank
Total Posts:  4
Joined  2015-03-10

Thank you for the suggestion. I don’t believe I do, do you recommend a software?

Profile
 
 
Posted: 10 March 2015 06:56 PM   [ Ignore ]   [ # 86 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

https://www.stattransfer.com/

They have a free trial. They do not specifically mention SAS version 9.4 though, so maybe try downloading it again first (maybe some error in the downloaded file?)

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 11 March 2015 11:01 AM   [ Ignore ]   [ # 87 ]
Newbie
Rank
Total Posts:  4
Joined  2015-03-10

got it, thank you!!

Profile
 
 
Posted: 11 July 2015 01:45 PM   [ Ignore ]   [ # 88 ]
Newbie
Rank
Total Posts:  4
Joined  2015-07-11

Hi everyone! I am also very new to Perl and I have no access to SAS, and I need to download the 10Ks after 2003 for my research. Since I can’t use the first part of the code (the SAS part), I downloaded the files you provided here (http://www.wrds.us/index.php/repository/view/25) and ran directly the Perl part of the code (the way I ran it was saving the Perl code to hello.pl and running directly from terminal the entire file with

perl hello.pl 

). I get the same error message mentioned by other members as well:
No such file or directory at hello.pl line 28.
I would be so very grateful if you could help me with any advice! It looks like by myself I could spend many other days trying to figure it out.
Thanks a lot!

Profile
 
 
Posted: 11 July 2015 07:36 PM   [ Ignore ]   [ # 89 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi maisha,

The files you mention you downloaded, is that the zipfile that holds a SAS dataset? Were you able to convert this and filter it? (It has all filings, 10-K filings are just a fraction).

It is not clear what is in file ‘hello.pl’, can you save this file as a .txt file and add it as an attachment to your forum post?

Best,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 16 July 2015 08:21 AM   [ Ignore ]   [ # 90 ]
Newbie
Rank
Total Posts:  4
Joined  2015-07-11

Hi Joost and thanks for your answer (and sorry for my late reply)! Yes I have downloaded the SAS files, converted them to Stata in order to filter only 10K files, then saved the result as a .txt file (I can’t attach the resulting file, 10Ks.txt, because it is too large, but just so you can get an idea on whether it looks ok I attach a sample of it). Then I ran the perl code in hello.pl (hellotxt.txt, attached). The program downloads some files now, but all of their contents have an error message from the SEC page. I attach one of the files downloaded by the program saved as .txt (all that I have checked look the same). It looks like the address from 10Ks.txt is not correct, right? If you have ever encountered this error or have any suggestions, it would be very helpful!
Thank you!
Elvira

File Attachments
hellotxt.txt  (File Size: 2KB - Downloads: 150)
10Ks_sample.txt  (File Size: 3KB - Downloads: 177)
A.C._Moore_Arts_Crafts.txt  (File Size: 3KB - Downloads: 148)
Profile
 
 
   
6 of 7
6