options source spool nocenter nonumber ps=max ls=200; title;
          libname mystore "/sastemp0/somewhere/";

          ** check out an instance of change in fiscal year-end;
          data test1 (keep=gvkey datadate fyear fyr ryearbeg ryearend);
            set comp.funda (keep=gvkey datadate datafmt indfmt consol popsrc
            fyear fyr);
            where GVKEY='158494' and DATAFMT='STD' and INDFMT='INDL'
            and CONSOL='C' and POPSRC='D' and missing(FYEAR)=0 and FYR ne 0;

            ** convert fiscal year into regular time range;
            ryearbeg=intnx('month','01JAN1960'd,intck('month','01JAN1960'd,
              mdy(fyr,1,fyear))+1-12*(fyr>5));
            ryearend=intnx('month','01jan1960'd,intck('month','01jan1960'd,
              mdy(fyr,1,fyear))+1+12*(fyr<=5))-1;
            format ryearbeg date9. ryearend date9.;

          proc print data=test1 width=min;
            run;
        

Here is my SAS code to compute the CUSIP/CINS check digit, and it was ran successfully against the CUSIP master file to verify its effectiveness.

            data test2 (drop=cusip_uc i t c v);
              set test1;
              cusip_uc=upcase(cusip);
              i = 1;
              t = 0;
              do while (i<=8);
                c = substr(cusip_uc,i,1);
                if anyalpha(c,1)=1 then v=rank(c)-rank('9')+2;
                else if anydigit(c,1)=1 then v=input(c,best32.);
                else if c eq '*' then v=36;
                else if c eq '@' then v=37;
                else if c eq '#' then v=38;
                else v=.;
                if mod(i,2)=0 then v = v *2;
                t = t + int(v/10) + mod(v,10);
                i=i+1;
              end;
              check = mod(10 - mod(t,10), 10);
              if input(substr(cusip,9,1),best32.) ne check;
              
            proc print data=test2 (obs=25);
            run;
        

Here is my SAS code to compute the ISIN check digit.

            data test2 (drop=isin_uc i j s t c v n);
              length s $22.;
              set test1;
              isin_uc=upcase(isin);
              i = 1;
              s = '';
              do while (i<=11);
                c = substr(isin_uc,i,1);
                if anyalpha(c,1)=1 then v=rank(c)-rank('9')+2;
                else if anydigit(c,1)=1 then v=input(c,best32.);
                else if c eq '*' then v=36;
                else if c eq '@' then v=37;
                else if c eq '#' then v=38;
                s = cats(s,v);
                i=i+1;
              end;
              j=1;
              t=0;
              do while (j<=lengthn(s));
                n=substr(s,j,1);
                v=input(n,best32.);
                if mod(j,2)=1 then v = v*2;
                t = t + int(v/10) + mod(v,10);
                j=j+1;
              end;
              check = mod(10 - mod(t,10), 10);
              if input(substr(isin,12,1),best32.) ne check;
              
            proc print data=test2 (obs=25);
            run;
        

Here is my SAS program for computing the monthly time series for stock dividend-yield. Basically, we find the total dividend payments on the value-weighted CRSP index over the previous 12 month and use the ratio of total dividends to the current level of the index as the annualized dividend-price ratio. You can easily modify the program to compute the annualized dividend yield, defined as the ratio of total dividends to the index level at the end of the previous year. The monthly series is achieved via repeating this procedure over the rolling 12-month period.

Here is my PERL script for downloading the factors from Ken French's website, uncompressing the zip file, and parsing the result into a CSV format file that can be directly imported into SAS. Running this script with the desired Series ID (see the list of IDs inside the script) allows you to have instantaneous access to the latest Fama French factors as well as the historical book equity data hand-collected from Moody's Manuals. Other niceties of this script include: converting the returns from percentage terms to decimal points, and resizing/transforming the 25 base portfolios into a flat file format.

Here is my PERL script for downloading from Federal Reserve Economic Data (also known as FRED II) and parsing the result into a CSV format file that can be directly imported into SAS. You need to find out the desired Series ID from FRED II at first, unless you are trying to get Moody's Seasoned Aaa/Baa Corporate Bond Yield or 30/20-Year Treasury Constant Maturity Rate. For the latter case, I have already written down the Series ID's inside the perl script. I personally use this script to construct the time series for term premium and default premium.

If you are interested in downloading more than one articles from JSTOR in an automatic fashion, you might be interested in checking out my perl script for this purpose. You can specify the journal code, volume number, issue number and month for the high resolution PDF articles to be downloaded automatically from JSTOR. You can even write a shell script to feed these inputs in a sequential fashion. Keep in mind, however, the perl script works only if you run it on a computer or server within the IP domain that has access rights to JSTOR. Similarly, you can use my perl scripts to download articles in PDF format from Journal of Financial Economics, Journal of Finance, Review of Financial Studies, Journal of Business and Journal of Financial Markets. Keep in mind that these perl scripts can be used to download other journals from the same publisher, by slightly modifying the code. Again, these scripts work only if you run them on a computer with IP access to the respective sources, and require fine tweaks whenever the content provider alters the web layout. Sometimes, you might have to use Lynx to download the source HTML files first and process the source files with my perl scripts. Once inside Lynx, you can use the command \ to view the source html file and then the printing command p to save the source file into a local drive. Alternatively, you can navigate to the link to the file that you want to download and press the download command d. You will be prompted with a few options, one of which allows you to save the document in a local folder. Read the user's guide for more details.

The National Archives provide Records on Trading of Securities by Corporate Insiders (ORS) via the Access to Archival Databases (AAD) System. The entire database covers 138 files spanning the period between 1978 and 2001. You can order an electronic copy of the database for a price of around $1,400. Alternatively, you can use the AAD system to get the records manually on-line. But the catch is that you are not allowed to download records with a file size larger than 150KB each time when you access the database. It does not restrict how many times you can access the database each day, however. I wrote a Perl script to automate the process of downloading the database from the National Archives. You will need to use the free SAMIE module for this purpose.

My Perl script allows you to download all the records from 134 files but incomplete records (missing about 10% of all records) for the last four files, each of which is more than 100MB in size. The reason why we can not download the complete records off the AAD for these four files is that the programmers who set up the AAD system did not set aside a special code for records with empty values for a key identifier (and there are no such records in the other 134 files). I was informed by the National Archives that the only way to get the records with empty key identifiers is, yeap, to pay hard cold cash. Those four files will set you back about $129. You need to know how to set up SAMIE and I do not provide technical support in this regard. You would have to spend a little time to justify the savings of more than $1,200, right? Enjoy. (Also see the section designated for PERL Programming.)

Here is my SAS program for converting the Compustat fiscal years and quarters into a regular time range so that the financial information can be compared across different firms over the same calendar time period. The annoted SAS program provides all the information that you ever want to know about the Compustat fiscal years/quarters as well as the so-called S&P calendar years/quarters. For more detailed information, you should try to read the definitions of Compustat data year and quarter, the definition of the fiscal-year end month, and S&P's note on accessing the annual and quarterly data. If you want to handle carefully the earnings announcement dates as earnings can be restated and/or revised, read the very informative note by Joshua Livnat.

Here are the relevant formula in my SAS program, for a quick reference.
To get the starting date of the fiscal year, use ryearbeg=intnx('month','01JAN1960'd,intck('month','01JAN1960'd, mdy(fyr,1,yeara))+1-12*(fyr>5));
To get the ending date of the fiscal year, use ryearend=intnx('month','01JAN1960'd,intck('month','01JAN1960'd, mdy(fyr,1,yeara))+1+12*(fyr<=5))-1;
To get the starting date of the fiscal quarter, use rqtrbeg=intnx('month','01JAN1960'd,intck('month','01JAN1960'd, mdy(fyr,1,yeara))+1+3*(qtr-1)*(fyr<=5)-3*(5-qtr)*(fyr>5));
To get the endign date of the fiscal quarter, use rqtrend=intnx('month','01JAN1960'd,intck('month','01JAN1960'd, mdy(fyr,1,yeara))+1+3*qtr*(fyr<=5)-3*(4-qtr)*(fyr>5))-1;

Here are the sample perl scripts that I use to call SAS interactively.

In the example below, I use PERL to call WinRAR and extract files from compressed archives.

            my $rar_command="C:\\Program Files\\WinRAR";
            # run the rar and extract the reports;
            open(RAR, "\"$rar_command\\rar.exe\" e -ep -y \"$rar_folder\\$rar_fname\"
              \"$sas_folder\\\" |") or die "Could not find rar command\n";
            close(RAR);
        

In the example below, I use PERL to call WinRAR and create newly compressed archives.

            my $rar_command="C:\\Program Files\\WinRAR";
            # run rar and compress the sas dataset
            open(RAR, "\"$rar_command\\rar.exe\" a -ep -df -y
              \"$sas_folder\\report$seg_type.rar\"
              \"$sas_folder\\report$seg_type.*\" |")
              or die "Could not find rar command\n";
            close(RAR);
        

Sometimes we need to write SAS code interactively so that the code varies with different variables or assignment of macros that are not easily handled by SAS Macro alone. Perl can play an important role here by allowing us write the SAS code on the fly and execute the resulting SAS code subsequently.

As a first step to achieve this goal, I use the following piece of Perl script to print a code segment between <<eof and eof that can be further customized. I can also embed a segment of base SAS code from another file. There are two key things to remember here. One, inside that code segment between <<eof and eof I would have to back-slash symbols such as $, ", ', etc. that have special meaning for Perl. Second, I have to ensure that the string terminator eof appears on a separate line by itself without surrounded by any leading or trailing spaces.

            # print the following block of sas code
            open (SASCODE, "> $sas_folder\\report$seg_type.sas")
              or die "could not open SASCODE file $!";
            print SASCODE <<eof;
            libname mystore \"$sas_folder\";

            data mystore.report$seg_type (drop=Num);
              %let _EFIERR_ = 0;
              infile \"$sas_folder\\$tab_fname\" delimiter = \'\|\'
                MISSOVER DSD lrecl=32767 firstobs=2;
              informat Num best32. ;
              informat Cusip \$10. ;
              format Num best12. ;
              format Cusip \$10. ;
              input
              Num
              Cusip \$;
              if _ERROR_ then call symputx(\'_EFIERR_\',1);
              run;
            eof
            # ensure no space or anything else around "eof"
            close (SASCODE) || die ("could not close SASCODE file $!");
        

As a second step, I can now use the following sample code to call SAS in a batch mode. I can also make it more jazzy by parsing through the SAS log file and pick out warning and/or error messages etc.

            # run the sas program
            my $sas_command="C:\\Program Files\\SAS\\SAS 9.1";
            open(SAS, "\"$sas_command\\sas.exe\"
              -sysin \"$sas_folder\\report$seg_type.sas\"
              -log \"$sas_folder\\report$seg_type.log\" |")
              or die "Could not find sas command\n";
            close(SAS);
        

Often times we need to merge two different databases by company names. The reality is that this is not a job as innocuous as it may sound. Identifiers can change over time and many databases even recycle the identifiers (say stock tickers). Although each database has its unique identifier, say PERMNO for the CRSP database and GVKEY for the COMPUSTAT database, sometimes the only feasible option left is to match by company names. The heart of the problem is that different databases use different abbreviation conventions for the firm names. For instance, what appears to be "holdings" in one database may show up as "HLDGS" in another database. Worse, "holdings" can show up for a short firm name while "HLDGS" shows up for a long firm name even within the same database. If we match databases by firm names without accounting for such conventions, the merge almost always leads to the unnecessary loss of firms. Here is where PERL becomes really handy as it can easily convert firm names for both databases prior to the merge. Check out my PERL script for this purpose, but keep in mind that this is not a panacea. To make it work for you, you need to understand a bit of PERL and make slight modifications to the code.

Here is my SAS program for replicating the momentum factors according to French's definition for UMD (up minus down). I have also written a SAS program for replicating the momentum factors according to Carhart's Definition (Journal of Finance, 1997, Volume 52, Pages 57-82). The correlation between the replicated monthly series using French's definition and the monthly series posted on Ken French's website is 0.9905 between 1927 and 2013. The average returns for these two monthly series are 68.68 basis points and 71.56 basis points, respectively, between 1927 and 2013. The correlation between the replicated monthly series using French's definition and the replicated series using Carhart's definition is 0.8424. It is trivial to modify the program to generate daily and weekly series.

Here is my PERL script for parsing the SEC filing form N-SAR, i.e., the semi-annual report for investment management companies (read, mutual funds). After parsing the source filings, you may use a simple SAS program to import all the post-parsed data into a SAS dataset in the panel data format of multiple rows per filing. I assign a unique file identification code for each source filing, and you may transpose the SAS dataset into the format of one row per filing. For further details on how this is accomplished, dig into the zip file containing the source codes, sample inputs and output.

Keep in mind though there are companies out there that specialize in parsing the SEC filings for you at a fee. SECINFO provides various SEC filings at the XML format. For your first 45 trials, this service is free. After that, it charges you about $10 a month. Edgar Online is another highly ranked data service providers. These two companies may or may not accept your request of parsing one particular form that you need, but there are smaller entities that cater to your special needs.

A firm formerly known as Disclosure Inc and later acquired by Thomson Financial has provided some interesting databases, including Compact D/SEC, Compact D/Worldscope and Compact D/New Issues. The poor user interface and the choice of the CD-ROM media really hinder them from being widely used. Due to the resentment of the Windows interface for the databases, I spent a lot of time fiddling with the command line interface instead. I had hoped to write a macro or script that is native to the database so as to automate the data extraction. Such hopes were crushed, however, due to the lack of documentation. Nevertheless, I dug out some command line help information and compiled the Commands Guide. I was able to use the command line interface to extract the output files with tagged fields in a semi-automatic fashion, as it takes me three lines of commands to extract the data from each CD. Finally I wrote a Perl script to further process the tagged data into a format I wanted, and you can slightly modify the field names to slice and dice the data in the way you wanted too.

Here are three programs: my SAS program for computing the rolling compounded returns, my SAS program for computing the rolling standard deviations, and my SAS program for computing the rolling stock betas. You can specify an arbitrary length of the rolling window and arbitrary sorting variables. If you do make custom modification to the programs, however, you should always compare the results you get from the modified SAS program to the manual calculation from Excel in order to ensure data accuracy.

Here are the sample codes for a list of SAS procedures that are often used in finance applications.

In the example below, we use the univariate procedure (additional syntax) to create decile breakpoints and assign stocks into one of ten size deciles. Although the rank procedure can also be used to form a given number of groups, it does not give us as much flexibility as the univariate procedure in cases that we need to get the breakpoints from a subset of data (say only NYSE stocks).

          ** assign the breakpoints by size;
          proc univariate data=test1 noprint;
            var me;
            by monthid;
            output out=test2 pctlpts= 10 to 90 by 10  pctlpre=mebp;
            run;

          ** merge the breakpoints into the main dataset;
          data test3 (drop=mebp10 mebp20 mebp30 mebp40 mebp50
            mebp60 mebp70 mebp80 mebp90);
            merge test1 (in=inbase) test2;
            by monthid;
            if inbase;
            if missing(me) then megrp=.;
            else if me <= mebp10 then megrp=1;
            else if mebp10 < me <= mebp20 then megrp=2;
            else if mebp20 < me <= mebp30 then megrp=3;
            else if mebp30 < me <= mebp40 then megrp=4;
            else if mebp40 < me <= mebp50 then megrp=5;
            else if mebp50 < me <= mebp60 then megrp=6;
            else if mebp60 < me <= mebp70 then megrp=7;
            else if mebp70 < me <= mebp80 then megrp=8;
            else if mebp80 < me <= mebp90 then megrp=9;
            else if me > mebp90 then megrp=10;
            run;
        

In the example below, we use the freq procedure (additional syntax) to get instances of multiple cusips corresponding to one ticker symbol.

          proc sort data=test1 out=test2 nodupkey;
            by symbol cusip;

          proc freq data=test2;
            tables symbol /noprint out=test3;

          proc print data=test3;
            where count>=2;
            run;
        

In the example below, we use the reg procedure (additional syntax) to collect the regression estimates.

          ods output ParameterEstimates = test1;
          ods output FitStatistics = test2;
          ods output NObs = test3;
          ods listing close;

          proc reg data=regdata;
            model yvar=xvar;
            by ordrank quarter;
            run;

          ods output close;
          ods listing;
        

Here are some short examples of using the SAS built-in perl engine to perform text search based on Perl regular expressions. Although they are not SAS procedures that we are trying to cover here, they can be quite a life saver sometimes. The following line identifies whether or not a CUSIP starts with an alphabet, i.e., the country code under the CUSIP International Numbering System (CINS): CINS=(prxmatch(prxparse('/[A-Z]/'),substr(cusip,1,1)));

We can also easily extract the first phrase (at least two characters long) out of a company name by issuing: NAME_PHRASE=prxchange(prxparse('s/^(\w{2,})\s(.*?)$/$1/'),1,name);

Check out the syntax to the prxparse, prxmatch, prxchange functions. It is also useful to check out the list of SAS functions related to characters and formatting.

The following sample code is essential to computing the adverse selection component of spread in an efficient way. As defined in Huang and Stoll (JFE 1996), see also Bessembinder (JFM 2003), the adverse selection component of spread requires the calculation of mid-point of the first quote thirty minutes after the time of the reference quote for a matched trade record. The data set test1 contains the detailed quote information with variables symbol date time bid ofr. The data set test2 contains the matched pairs of trade and quote information with variables symbol date time price qtime. The goal is to identify for each qtime in test2 the first quote time in test1 that is thirty minutes after the qtime in test1. To make the program run efficiently, we need to work with a selected block of records in test1 corresponding to each record in test2, while trying to avoid looping through all records in test1 for each record of test2. The cartesian match in SQL would make it run really slowly especially for huge dataset, so we try to use the array access with direct pointers to solve the problem. Put the total number of records in test2 in a macro variable named combotot, and put the trades and quotes into half hour slots. Run through the entire data set of test2 once to generate a list of pointers, with obsbeg being the starting point of the half-hour slot that is immediately after the half-hour slot the trade time is in and obsendmax being the observation number for the daily closing quote. Put these pointers inside data set test2 as well. We use two set statements in the code to perform the dynamic pointing. The first set statement reaches the records from test2 one at a time, and save the corresponding qtime obsbeg obsendmax into the temporary arrays. The second set statement go to the data set test1 directly starting at the observation obsbeg, and sequentially evaluate whether or not thirty minutes have passed since the time of the reference quote qtime. Whenever this condition is satisfied, the program stops reading any more records from test2 and thus achieves great run-time efficiency.

          ** obtain the mid-price from the first quote thirty minutes away;
          data test3 (drop=obsbeg obsendmax);
            array _qtime{&combotot} _temporary_;
            array _obsbeg{&combotot} _temporary_;
            array _obsend{&combotot} _temporary_;
            keep symbol date qtime obsbeg obsendmax qtime30min midprc30min;
            set test2;
            _qtime[_n_]=qtime;
            _obsbeg[_n_]=obsbeg;
            _obsend[_n_]=obsendmax;
            do i=_obsbeg[_n_] to _obsend[_n_];
              set test1 point=i;
              if time-_qtime[_n_]>=1800 then go to endloop;
            end;
            endloop:
            QTIME30MIN=time;
            format time30min time.;
            MIDPRC30MIN=(bid+ofr)/2;
            output;
            run;
        

It is also worth checking out the SAS built-in Finance functions. You can also download the PDF version of it (pages 693-734 of SAS Language Reference: Dictionary, 4th edition).

The price, dividend, shares, and volume data from CRSP have already been adjusted for split events. Sometimes the need arises for manually adjusting price or shares for split events. For this purpose, we can obtain from CRSP the date ranges as well as the corresponding cumulative adjustment factors. Here is the relevant portion of CRSP manual regarding CRSP calculations. You may also want to check out the definitions of factor to adjust price and shares.

Here is my SAS program for getting the date range as well as the cumulative adjustment factors.

                        
            ** get the unique list of permno;
            proc sort data=mystore.csplist (keep=permno) out=test1 nodupkey;
              by permno;

            ** extract the daily cumulative adjustment factors;
            proc sql;
              create table test2 as
              select b.permno,b.cfacshr,b.date
              from crsp.dsf as b,test1 as k
              where b.permno=k.permno
              order by permno,date;
              quit;

            ** identify the date range for cumulative adjustment factor for shares;
            data test3 (drop=lcfacshr cfacshr);
              set test2;
              retain lcfacshr CFACSHROUT CSS_BEG CSS_END;
              format css_beg date9. css_end date9.;
              by permno date;
              if first.permno and last.permno then do;
                css_beg=date;
                css_end=date;
                cfacshrout=cfacshr;
                output;
                end;
              else if first.permno and not (last.permno) then do;
                css_beg=date;
                css_end=date;
                cfacshrout=cfacshr;
                end;
              else if not (first.permno) and not (last.permno) then do;
                if cfacshr=lcfacshr then do;
                  css_end=date;
                  cfacshrout=cfacshr;
                  end;
                else do;
                  output;
                  css_beg=date;
                  css_end=date;
                  cfacshrout=cfacshr;
                  end;
                end;
              else if not (first.permno) and last.permno then do;
                if cfacshr=lcfacshr then do;
                  css_end=date;
                  cfacshrout=cfacshr;
                  output;
                  end;
                else do;
                  output;
                  css_beg=date;
                  css_end=date;
                  cfacshrout=cfacshr;
                  output;
                  end;
                end;
              lcfacshr=cfacshr;

            data mystore.csslist;
              set test3;
              rename cfacshrout=CFACSHR;
              run;
                        
                    

It seems that WRDS provides the unadjusted series from CRSP. To see if the version of CRSP data you are using is adjusted or not, run the following short code segment.

                        
            ** check out MSFT split event;
            proc print data=crsp.dsf;
              where permno=10107 and '01MAR1990'd<=date<='30APR1990'd;
              var permno date ticker prc shrout vol facshr facpr cfacshr cfacpr;
              run;
                        
                    

Here are a few segments of SAS codes to initiate the Remote Submission session with the WRDS servers.

                        
          ** initiate the remonte session;
          %let wrds = wrds.wharton.upenn.edu 4016;
          options comamid=TCP remote=wrds;
          signon username=_prompt_;

          ** fill in your code block to be executed remotely;
          rsubmit;
          options source spool nocenter nonumber ps=max ls=200; title;
          libname mystore "/sastemp0/somewhere/";

          ** ------------------------;
          ** put your code block here;
          ** ------------------------;

          endrsubmit;