\documentclass[11pt]{article}
\usepackage{natbib}
\usepackage{fullpage}
\usepackage{url}
\usepackage{hyperref}
\title{SAS for Finance PhDs}
\author{Andr\'{e} de Souza}
\begin{document}
\maketitle
\bibliographystyle{jf}
\section{Purpose of this document}
This is not an introduction to SAS. Nor is it a manual on how to do specific things that a finance PhD might be interested in doing: that has been done very adequately by \cite{boehmeretal}.

Rather, it is a description of a set of tools and constructs that I use frequently in my data work; a set which is likely to be of help to researchers and PhD students doing similar data work.

Should you find this useful, or have comments that I might find useful, please email me: \url{mailto:adesouza@stern.nyu.edu}. 

\section{Where to get help for SAS problems}
SAS-L, the SAS users' mailing list, is archived here: \url{http://www.listserv.uga.edu/archives/sas-l.html}. I find it tedious to search. A better solution is to use Google. When you search for any SAS-related term, you're very likely to find research notes written for SAS USer Group (SUG) meetings. SAS User Groups meet frequently all over the US and the world, and publish notes which describe specific features of SAS. Many of the references of this document are such notes. 

The first place to go when you want to learn how to use a new feature of SAS (as opposed to being stuck on a particular problem) is an SUG publication (there may be several) on that topic. For instance, to learn about arrays in SAS, you could read ``Arrays Made Easy: An Introduction to Arrays and Array Processing'' \url{http://www2.sas.com/proceedings/sugi30/242-30.pdf}. Almost all these notes are easy to understand, and have detailed examples. 

\section{Some preliminaries you should know}
   \subsection{SAS basics}
   I assume you know how to read data into SAS, create a dataset, and run a data step. I assume familiarity with code that says
   \begin{verbatim}
     data first(rename=(ret=unsquared_ret));
         set crsp.msf(keep=permno date ret);
	 squared_ret=ret**2;
     run;
   \end{verbatim}
   You should also know how to sort datasets and run, say, proc means:
   \begin{verbatim}
     proc sort data=first;
         by date;
     run;
     proc means data=first;
         by date;
	 var ret;
     run;
   \end{verbatim}
   
   You should know how to redirect output:
   \begin{verbatim}
     proc sort data=first;
         by date;
     run;
     proc means data=first noprint;
         by date;
	 var ret;
	 output out=sumrets sum(ret)=sum_ret;
     run;
   \end{verbatim}
				   
   \subsection{The logic of the DATA step}
   \label{subsec:datasteplogic}
   There are subtleties to the DATA step that it would help to understand.

   The DATA step is nothing but an implicit loop. The standard DATA step looks like this:
   
   \begin{verbatim}
      data a;
         set b;
	 <do something>
      run;
   \end{verbatim}
   
   What this does is to read from dataset b, one observation at a time, do something to that observation, and then output that observation to dataset a. This is an implicit loop, which in pseudocode, might be made explicit by saying:
   
   \begin{verbatim}
     DO I = 1 TO ROWS(B);
         CURRENT_OBSERVATION=B[I,.];
         <do something to CURRENT_OBSERVATION>;
	 OUTPUT A;
     END;
   \end{verbatim}

   treating A and B as matrices so ROWS(B) and B[I,.] (by which I mean the \textit{Ith} row of the matrix B -- the \textit{Ith} observation) --  have meaning.
   
   There are two things that it is important to know. One is the role of the OUTPUT statement. The other is the role of the counter \_N\_.
   
   \subsubsection{The OUTPUT statement}
   The output statement in a DATA step outputs the observation in its current state to the dataset named on the data line (or all datasets on the data line if no one is specified). Consider the example below:
   
   \begin{verbatim}
     data first first_positive;
         set crsp.msf(keep=permno date ret);
	 output first;
	 if ret>0 then output first_positive;
     run;
   \end{verbatim}
   
   The dataset first contains all observations read from crsp.msf; the dataset first\_positive contains those for which the returns were positive. 
				   
   There are two things to note about output statements. First, every data step contains an implicit output statement. If there is no output statement in a dataset, then the observation is output as-is at the end of the data step. The two data steps below are identical:

   \begin{verbatim}
     data first;
         set crsp.msf(keep=permno date ret);
	 output first;
     run;

     data first;
         set crsp.msf(keep=permno date ret);
     run;

   \end{verbatim}

   Second, when there is an explicit output statement in a data step, the implicit output statement is turned off. 
   
   \begin{verbatim}
     data first first_positive;
         set crsp.msf(keep=permno date ret);
	 if ret>0 then output first_positive;
     run;
   \end{verbatim}
   
   If you run this code, the dataset first has zero observations, since (a) there is an explicit output statement in the data step thus turning the implicit output statement off and (b) no output statement references the dataset first. 

   \subsubsection{The counter \_N\_}
   \label{subsubsec:thecountern}
   The counter \_N\_ counts the iterations through the implicit loop of the DATA step. It increments when the top of the loop is encountered. 
   
   Suppose dataset B consists of one variable, called INDEX, with values 1-10, in order.
   
   \begin{verbatim}
                                 Obs    INDEX

                                   1       1 
                                   2       2 
                                   3       3 
                                   4       4 
                                   5       5 
                                   6       6 
                                   7       7 
                                   8       8 
                                   9       9 
                                  10      10 
   \end{verbatim}

   If I run this code
   \begin{verbatim}
     data b;
        set b;
	counter=_N_;
      run;
   \end{verbatim}
   
I get
\begin{verbatim}

                            Obs    INDEX    counter

                              1       1         1  
                              2       2         2  
                              3       3         3  
                              4       4         4  
                              5       5         5  
                              6       6         6  
                              7       7         7  
                              8       8         8  
                              9       9         9  
                             10      10        10  

\end{verbatim}

Suppose instead I were to run:

\begin{verbatim}
     data b;
        set b;
	counter=_N_;
	_N_=1;
      run;
   \end{verbatim}
   
one would imagine that counter would always have the value 2, since \_N\_ is being reset to 1 at every iteration of the data step loop. But this is not the case:

\begin{verbatim}

                            Obs    INDEX    counter

                              1       1         1  
                              2       2         2  
                              3       3         3  
                              4       4         4  
                              5       5         5  
                              6       6         6  
                              7       7         7  
                              8       8         8  
                              9       9         9  
                             10      10        10  

\end{verbatim}

This means that \_N\_ is only a place that SAS puts the value of an internal counter \textit{which we never see} and \textit{have no way of changing}. Whatever you do to \_N\_ in the data step, when the loop comes to the head of the DATA step, SAS will place the incremented value of the internal counter in \_N\_.

One useful construct is:

\begin{verbatim}
     data b;
        set b;
	if _n_ =1 then do;
	    <some stuff>
	    end;
      run;
   \end{verbatim}

which does  ``some stuff'' at the first value read from b and nowhere else. This is useful for initializing variables and hashes (see section~\ref{sec:merges}).




   
   \subsection{Some CRSP variable names}
   Since this is intended for finance PhDs, the examples often use CRSP data\footnote{Also see the section on WRDS datasets.}. If you are unfamiliar with CRSP, all you need to know is here.
   
   Stocks may be identified by CUSIP, ticker, or name. Stocks in CRSP are identified by PERMNO, which is very useful because it does not change when the company changes superficially, in contrast with, say, CUSIPs, which may change after companies change their names. The monthly stock file on WRDS, crsp.msf or /wrds/crsp/sasdata/msf.sas7bdat has a host of monthly stock return information, but the variables of most interest to us are
   \begin{verbatim}
     PERMNO   DATE    RET
   \end{verbatim}
   For the stock identified by PERMNO, RET is the holding period return from the beginning of the month to the end of the month.

   \subsection{Macro basics}
   Much of the text relies on a knowledge of SAS's macro language. I assume that you know what a macro variable is, how to create one (via \textit{call symput} or explicit \textit{\%let} statements), and how to create and run macros. If you do not know this, your life will probably be immeasurably improved by learning it. A good place to start is the SUG paper by Delwiche and Slaughter (whom you may remember from The Little SAS Book): \cite{delwslaught}.

\section{Move to SAS on Unix}
If you have not done so already, I strongly urge you to move to SAS on Unix, rather than using SAS on your desktop. Using SAS on your desktop means that:

\begin{itemize}
\item You can run only one SAS program at a time
\item You are limited by the amount of space and memory your PC has
\item When you run programs that use lots of resources your computer hangs
\item You can't access your programs from anywhere (unless you have Remote Desktop set up)
\item You are compelled to use SAS's editor, which is almost as bad as Notepad.
\end{itemize}

None of these limits apply to SAS on Unix. Of course, in the first two points above, these limits are replaced by larger limits. 

Moreover, WRDS is much simpler to access via SAS on the WRDS server than it is via the web interface. Your control over the data you extract from WRDS' datasets is greatly increased\footnote{In some instances, you may actually prefer to use the Web interface to extract some data, which would be complicated to merge in using the Unix interface, but these instances are not many.}.

\subsection{Cygwin and X-Windows}
While you can get by using PuTTY (available from \url{http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html}), I recommend that you go the whole hog and download Cygwin (\url{http://www.cygwin.com}), because it effectively turns your Windows PC into a Unix server. It gives you X-Windows when you connect to servers, which means, among other things, that you can get font highlighting in Emacs in colour. 

I also recommend you learn to use Emacs. The learning curve is somewhat steep, but you will find it well worth the effort. Emacs is available with Cygwin, and is installed on virtually all Unix machines, so even when you use PuTTY to connect to a server, you can always say 

\begin{verbatim}
  emacs -nw myfile.sas
\end{verbatim}
at the command line to start up Emacs. The ``-nw'' (no window) tells Emacs that you are not running X-windows.

One disadvantage of using SAS on Windows is that you are compelled to use SAS's editor, which does not have one-tenth of the features you will come to expect from editors after using Emacs\footnote{This is not, strictly speaking, true. You can run SAS in batch mode at Cygwin's command line, but I am talking about SAS installed on Windows \textit{without} Cygwin, as opposed to SAS on a machine with Cygwin installed.}

\section{Reading in datasets}
\subsection{Quick and dirty: convert proc import log output to an input statement}
Reading in CSV or otherwise delimited text files using proc import does not give you the control you want over how the variables are defined, but using an input statement means you have to type out all the (possibly tens of) variable names and format definitions. It also means that you have to know all your variable names and what exactly SAS's name for the delimiter is and a whole host of other things. 

A useful way to have control while not doing too much work is to use proc import on a small subsample first, and then use what proc import writes to the log to construct your own input statement, changing variable names or formats as you like.

For instance, I have the MSF dataset stored as a CSV file: text.csv. The first two lines are
   \begin{verbatim}
     PERMNO,DATE,RET
     10000,19860228,-0.257143
   \end{verbatim}
A proc import would read:
   \begin{verbatim}
     proc import file='text.csv' out=out dbms=csv;
     run;
   \end{verbatim}
and produce the following in the log file:
   \begin{verbatim}
11             data WORK.OUT                                     ;
12             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
13             infile 'text' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
14                informat PERMNO best32. ;
15                informat DATE best32. ;
16                informat RET best32. ;
17                format PERMNO best12. ;
18                format DATE best12. ;
19                format RET best12. ;
20             input
21                         PERMNO
22                         DATE
23                         RET
24             ;
25             if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
26             run;
   \end{verbatim}
Notice that the date variable is stored as a number, and it would have been a hassle to get it into the correct format.

But working from this log, it is trivial to produce the following clean input data step:
\begin{verbatim}
  data WORK.OUT;
  infile 'text' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2;
  informat PERMNO best32. ;
  informat DATE YYMMDD8. ;
  informat RET best32. ;
  format PERMNO best12. ;
  format DATE date9. ;
  format RET best12. ;
  input
      PERMNO
      DATE
      RET
  ;
 run;
   \end{verbatim}

Notice the change in the informat and format for date, so that the output dataset has date correctly coded.

\subsection{guessingrows}
Proc Import uses the first 20 rows of your data file to decide what kind of data each variable is. If your data is such that the first 20 rows are not enough, you can use the guessingrows option in proc import:

   \begin{verbatim}
     proc import file='text.csv' out=out dbms=csv;
     guessingrows=300;
     run;
   \end{verbatim}

This is useful if you have mixed  character and numeric variables for which the first 20 observations are all numeric.


\subsection{Why does proc import hang on Unix?}
\label{subsec:procimphangs}
In some circumstances, when you run a SAS program on Unix with proc import (or proc export), the program reaches the import step and just sits there, not moving forward nor dying with an error. What may be happening is that proc import is trying to open an X-window telling you that it is running, and you are not running an X-windowing system.

To get around this, at the command prompt type 

   \begin{verbatim}
     sas -noterminal myfile.sas &
   \end{verbatim}
instead of
   \begin{verbatim}
     sas myfile.sas &
   \end{verbatim}

This prevents SAS from trying to open an X-window to tell you about the progress of proc import. This also works with other procs that try to open windows: for instance, proc gplot.

\section{Working with WRDS datasets}
\subsection{CRSP: DSF/MSF}
\label{subsec:pwd1}
\begin{itemize}
\item The date in the MSF file is not the end-of-month, but the last trading day of each month. To avoid errors, I always reset the date when I open crsp.msf:
   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
	date=intnx('month', date, 1)-1;
     run;
   \end{verbatim}
\item Closing prices in the MSF and DSF files are negative if the price is not the price from an actual trade, but the average of bid-ask spreads. I always set prc=abs(prc) before I do anything else with prices.
\end{itemize}

\subsection{Names files in CRSP: stocknames and fundnames}
\label{subsec:pwd2}
Aside from the DSF and MSF files (and analogous files for mutual funds), data that changes infrequently and irregularly for each permno or crsp\_fundno (like company name or ticker or exchange) is stored in names files. These files have the form, for instance

   \begin{verbatim}
     PERMNO NAME BEGINNING_DATE ENDING_DATE
   \end{verbatim}
   where BEGINNING\_DATE is the first date on which that PERMNO has that NAME (or ticker, or ncusip, or whatever), and ENDING\_DATE is the last date on which that PERMNO has that NAME.
   
For stocks, this file is crsp.stocknames, for funds, it is crsp.fundnames. Other files stored in this format include crsp.fund\_fees and crsp.fund\_style.

Consider stocknames. The BEGINNING\_DATE and ENDING\_DATE variables here are named NAMEDT and NAMEENDDT. To change stocknames into a dataset that can be merged with, say, msf, I can do
   \begin{verbatim}
     data stocknames(keep=permno date ticker);
        set crsp.stocknames(keep=namedt nameenddt permno ticker);
	where namedt is not missing;
	date=intnx('month', namedt, 1)-1; 
	do while(date<=nameenddt);
	   output;
	   date=intnx('month', date+1, 1)-1;
	   end;
     run;
     data msf;
        set crsp.msf(keep=permno date ret);
	date=intnx('month', date, 1)-1;
     run;

   \end{verbatim}
   and then merge the two by permno and date.

   The statement 
   \begin{verbatim}
     date=intnx('month', namedt, 1)-1; 
   \end{verbatim}
   moves the date to  the end of the month in which namedt lies. 

   If you do not include the ``where namedt is not missing'', missing namedts will result in an infinite loop, since SAS treats the missing value as equal to negative infinity in numerical comparisons. 

   There are special missing values for namedt and nameenddt given by ``.B'' and ``.E'', to indicate the ``Beginning'' of time and the ``End'' of time respectively. If you want to include these observations, you must say:

   \begin{verbatim}
     data stocknames(keep=permno date ticker);
        set crsp.stocknames(keep=namedt nameenddt permno ticket);
	if namedt=.B then namedt='01Jan1900'd;
	if nameenddt=.E then namedt='31Dec2020'd;
	if not(missing(namedt));
	date=intnx('month', namedt, 1)-1; 
	do while(date<=nameenddt);
	   output;
	   date=intnx('month', date+1, 1)-1;
	   end;
     run;
   \end{verbatim}
   That is, you allow SAS to read in missing values, change them appropriately, and delete observations where namedt is still missing. Notice that the statement says ``if namedt=.B'', without any quotes around ``.B''. Also notice that the  ``namedt is not missing'' has become ``not(missing(namedt))''. SAS does not support the use of the ``is not missing'' construct outside \textit{where} statements.

\subsection{CRSP: ncusip and cusip}
The CUSIP that CRSP reports is the so-called ``header CUSIP'', which is the last CUSIP belonging to that PERMNO. A PERMNO may have had several different CUSIPs in the past. These are called the  ``names CUSIPs'' (NCUSIPs) by CRSP. 

Suppose I have data about stocks that only has historical CUSIPs as identifiers. Examples include holdings data, governance data, and mutual fund voting data. To convert these into PERMNOs, I could use the stocknames file, doing as suggested by Subsection \ref{subsec:pwd2}. Alternatively, I could use the fact that CUSIPS are not reused in the data:

   \begin{verbatim}
     proc sort data=crsp.stocknames(keep=permno cusip) out=permnocusip(rename=(cusip=ncusip)) nodupkey;
        where cusip is not missing;
        by permno cusip;
     run;

     proc sort data=crsp.stocknames(keep=permno ncusip) out=permnoncusip nodupkey;
        where ncusip is not missing;
        by permno ncusip;
     run;

     proc append base=permnoncusip data=permnocusip;
     run;

     proc sort data=permnoncusip nodupkey;
        by permno ncusip;
     run;
   \end{verbatim}

   The last file is a permno-ncusip lookup table, which I can merge with my CUSIP data to obtain permnos.

   Why did I include CUSIPs as well? One would expect that the NCUSIP history perfectly accounted for even the last CUSIP held - the header CUSIP. But it does not always. 

\subsection{Adjusting shares in holdings data}
A constant problem in using holdings data is adjusting the shares held for stock splits between the reported date and any date in the future. 

For instance, if you are calculating the change in shares of Adobe held by a mutual fund from January 1, 2005 to  December 31, 2005, you need to adjust the shares held on January 1 for splits over the year before you can subtract them from the shares held in December.

The way to do this is to use CRSP's CFACSHR variable, available in the MSF file. To adjust shares reported on \textit{reported date} to another date, \textit{desired date}, I say 

\begin{verbatim}
shares_reported_adjusted_to_desired_date=shares_reported*cfacshr_reported_date/cfacshr_desired_date
\end{verbatim}

Suppose the fund held 2000 shares of Adobe(PERMNO 75510) on January 1, 2005 and 4500 shares on December 31, 2005. 

If I say 
\begin{verbatim}
proc print data=crsp.msf;
   where permno=75510 and date>='01Dec2004'd and date<='31Dec2005'd;
   var permno date ret prc cfacshr;
run;
\end{verbatim}
SAS produces:
\begin{verbatim}
PERMNO        DATE            RET             PRC    CFACSHR	
													
 75510    20041231       0.036204        62.74000       2   	
 75510    20050131      -0.093083        56.90000       2   	
 75510    20050228       0.085237        61.75000       2   	
 75510    20050331       0.087976        67.17000       2   	
 75510    20050429      -0.114634        59.47000       2   	
 75510    20050531       0.113839        33.12000       1   	
 75510    20050630      -0.136171        28.61000       1   	
 75510    20050729       0.036001        29.64000       1   	
 75510    20050831      -0.087719        27.04000       1   	
 75510    20050930       0.103920        29.85000       1   	
 75510    20051031       0.080402        32.25000       1   	
 75510    20051130       0.011163        32.61000       1   	
 75510    20051230       0.133395        36.96000       1      

\end{verbatim}

Apparently, there was a 2-for-1 stock split between the end of April and the end of May. The 2000 shares in January would have become 4000 shares in December. The formula above yields:

shares in January adjusted to December=shares in january*January cfacshr/December cfacshr

Or

shares in January adjusted to December=shares in january*2/1

Or

shares in January adjusted to December=2000*2/1=4000

Therefore the increase in shares is 500 ``December shares''. The value of this trade, assuming it happened in December, is 500*36.96, which is the December price. 
\subsection{Merging CRSP and Compustat}
				   
\section{Working with time-series data and dates}

\subsection{Moving dates around}
A simplistic way to work with dates with monthly data in SAS is to convert all dates to a year and month, and then use those. For instance

   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
	year=year(date);
	month=month(date);
     run;
     proc sort data=msf;
        by permno year month;
     run;

     proc means data=msf;
        by permno year;
	var ret;
     run;
     \end{verbatim}

This is appealing when, say,  you have to calculate within-year averages, but it can quickly get tedious if you have to do other simple tasks. For instance, suppose you want to create a series of *daily* dates from January 1, 1961 to December 31, 1962. 

   \begin{verbatim}
     data dates;
	 do year=1961 to 1962;
	    do month=1 to 12;
	       lengthofmonth=31;
    	       if month=4 or month=6 or month=9 or month=11 then lengthofmonth=30;
 	       if month=2 then lengthofmonth=28;
 	       do day=1 to lengthofmonth;
	          output;
	       end;
	    end;
	  end;
     run;
     \end{verbatim}


This is terribly tedious - not to mention fraught with the danger that you'll make errors in leap years. The alternative is to use SAS' intnx function, which increments dates by specified intervals.


   \begin{verbatim}
     data dates;
         date='01Jan1961'd;
	 do while(date<='31Dec1962'd);
	     output;
	     date=intnx('day', date, 1);
	  end;
     run;
     \end{verbatim}
The intnx function increments date (its second argument), by 1 unit (the third argument) of 'day' (its first argument). 

The more astute among you will observe that I get the same effect by saying

   \begin{verbatim}
     data dates;
         date='01Jan1961'd;
	 do while(date<='31Dec1962'd);
	     output;
	     date=date+1;
	  end;
     run;
     \end{verbatim}

because dates are treated as numbers in SAS. This is well and good if I want to increment by single days. Suppose instead I want to increment by weekdays. That is, I want the list of all weekdays in 1961 and 1962. An easy way to do this is:

   \begin{verbatim}
     data dates;
         date='01Jan1961'd;
	 do while(date<='31Dec1962'd);
	     output;
	     date=intnx('weekday', date, 1);
	  end;
     run;
     \end{verbatim}
which is a useful construct in working with the DSF file, and horrendously complicated to do any other way. 

The intnx function, as already said, increments dates, but it does so in a nonintuitive way. Suppose you are incrementing by year. 

   \begin{verbatim}
     date=intnx('year', date, 1);
   \end{verbatim}
   Intnx moves the date to the first day of the subsequent year, \textit{not} forward by one twelve-month period. If you say 'month' instead of 'year', intnx moves the date forward to the first day of the next month however many times you specify. Saying

   \begin{verbatim}
     date=intnx('month', date, 0);
   \end{verbatim}
   
   moves the date to the beginning of the month - the first of the month- in which date falls. Similarly for 'qtr'. 


I commonly use the intnx function to do the following:
\begin{itemize}
  \item Move the date to the end of the month/quarter/year
    \begin{verbatim}
      date=intnx('month', date, 1)-1;
      date=intnx('qtr', date, 1)-1;
      date=intnx('year', date, 1)-1;
    \end{verbatim}
  \item Move the date to the beginning of the month/quarter/year
    \begin{verbatim}
      date=intnx('month', date, 0);
      date=intnx('qtr', date, 0);
      date=intnx('year', date, 0);
    \end{verbatim}
  \item Move the date to the end of the previous month
    \begin{verbatim}
      date=intnx('month', date, 0)-1;
    \end{verbatim}
  \item Move the date (which is an end-of-month) to the end of the next month
    \begin{verbatim}
      date=intnx('month', date+1, 1)-1;
    \end{verbatim}
    Observe here that I have to say date+1. Since the date is currently of the form 31Jan1999, saying 
    \begin{verbatim}
      date=intnx('month', date, 1)-1;
    \end{verbatim}
    will leave us with 01Feb1999-1, or 31Jan1999.

  \item Move the date to the end of month 12 months ago
    \begin{verbatim}
      date=intnx('month', date, -11)-1;
    \end{verbatim}
  \item Move the date (which is an end-of-month) to the end of the 12 months ahead
    \begin{verbatim}
      date=intnx('month', date+1, 12)-1;
    \end{verbatim}
  \item Given a beginning date and an ending date, create a dataset with all the month-ends in between:
    \begin{verbatim}
      data beg_end;
         beginning_date='31Jan1996'd;
         ending_date='31Dec2007'd;
      run;
      data all_dates;
         set beg_end;
	 date= beginning_date;
	 date=intnx('month', date, 1)-1;
	 do while(date<=ending_date);
	    output;
	    date=intnx('month', date+1, 1)-1;
	 end;
      run;
    \end{verbatim}
    The first 
    \begin{verbatim}
	 date=intnx('month', date, 1)-1;
    \end{verbatim}
    statement is to ensure that the first date is an end-of-month. 

    \item Produce a list of all Wednesdays between two dates:
      
      \begin{verbatim}
      data beg_end;
         beginning_date='31Jan1996'd;
         ending_date='31Dec2007'd;
      run;

      data all_dates;
          format date date9.;
          set beg_end;
          date=beginning_date;
          if weekday(date)>4 then date=intnx('week', date, 1)+3;
          else date=intnx('week', date, 0)+3;
          do while(date<=ending_date);
              output;
              date=intnx('week', date, 1)+3;
              end;
      run;
            
\end{verbatim}      
The weekday() function returns the day of the week, 1 being Sunday. I make the first date a Wednesday (weekday=4) with 
\begin{verbatim}      
          if weekday(date)>4 then date=intnx('week', date, 1)+3;
          else date=intnx('week', date, 0)+3;
\end{verbatim}      
The logic is simple. If the current date is ``more'' than a Wednesday (ie, weekday(date)>4), then the first admissible date is Wednesday of next week. Sunday of next week is 
\begin{verbatim}      
       date=intnx('week', date, 1);
\end{verbatim}      
Add 3 to that to get to Wednesday. If the current date is ``less than or equal to'' Wednesday, then go to the beginning of this week
\begin{verbatim}      
       date=intnx('week', date, 0);
\end{verbatim}      
and add 3 to \textit{that}.

The rest of the scheme is obvious. This is useful in constructing weekly returns (Thursday-to-Wednesday to avoid any weekend effect).

\end{itemize}
				   
\subsection{Avoiding the lag() function}
\label{subsec:avoidlag}
A common task in time-series data is to find the lag of a variable: that is, the value the variable took in the previous period. Suppose I wanted to find the value of return in the previous month for every permno at every month-end. I could do this by saying:
   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
	date=intnx('month', date, 1)-1;
        lagret=lag(ret);
     run;
   \end{verbatim}
   If you're puzzled by the 

   \begin{verbatim}
        date=intnx('month', date, 1)-1;
   \end{verbatim}   
   
statement, please see section \ref{subsec:pwd1} for an explanation.

   One problem with this is that the first value of lagret for each permno is set to the last value of ret for the previous permno. To avoid this, I write
   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
	by permno;
        date=intnx('month', date, 1)-1;
        lagret=lag(ret);
	if first.permno then lagret=.;
     run;
   \end{verbatim}
   Observe that I did not write:
   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
	by permno;
        date=intnx('month', date, 1)-1;
	if not(first.permno) then lagret=lag(ret);
     run;
   \end{verbatim}
   which, at first glance, seems equivalent. The problem is that the lag() function pushes the current variable value onto a stack every time \textit{it is called}, and returns that value the next time it is called. This means that if the lag() function is not called on \textit{every} observation, it will give counterintuitive results. In the code just shown, lag() is not being called for the first observation of each permno.
Using the lag() function poses another problem. Suppose the value of return is missing in a particular month, as for July 1986 for permno 10000 below:

\begin{verbatim}
 PERMNO        DATE            RET

  10000    19860228      -0.257143
  10000    19860331       0.365385
  10000    19860430      -0.098592
  10000    19860531      -0.222656
  10000    19860630      -0.005025
  10000    19860831      -0.615385
  10000    19860930      -0.057143
  10000    19861031      -0.242424
  10000    19861130       0.060000
  10000    19861231      -0.377358
  10000    19870130      -0.212121
  10000    19870228       0.000000
  10000    19870331      -0.384615
  10000    19870430      -0.062500
  10000    19870531      -0.066667
  10001    19860228       0.020408
  10001    19860331       0.025200
  10001    19860430       0.009901
  10001    19860531      -0.009804
\end{verbatim}

This would obviously create trouble for the approach above. One way to handle this would be to say:

   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
	by permno;
        date=intnx('month', date, 1)-1;
        lagret=lag(ret);
	lagdate=lag(date);
	if first.permno then lagret=.;
	if not(lagdate=intnx('month', date, 0)-1) then lagret=.;
     run;
   \end{verbatim}

which checks to see if the lagged date is indeed a month previous to the current date. This is inelegant, and difficult to extend, if, for instance, you have several variables to lag.

Instead, do the following:

   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret);
        date=intnx('month', date, 1)-1;
     run;

     data msflag(rename=(ret=lagret));
        set msf;
	date=intnx('month', date+1, 1)-1;
     run;
     
     data msf;
        merge msf msflag;
        by permno date;
     run;
   \end{verbatim}

This creates a new dataset msflag with all variables identical, except that the date is moved forward one month. This means that, in msflag, the return for January 1986 for each permno is going to have the date set to February of 1986. When you merge msflag with msf (by permno and date), the returns line up exactly as you want them to. Explicitly, msflag looks like this:

\begin{verbatim}
 PERMNO        DATE        LAGRET

  10000    19860331      -0.257143
  10000    19860430       0.365385
  10000    19860531      -0.098592
  10000    19860630      -0.222656
  10000    19860731      -0.005025
  10000    19860930      -0.615385
  10000    19861031      -0.057143
  10000    19861130      -0.242424
  10000    19861231       0.060000
  10000    19870131      -0.377358
  10000    19870228      -0.212121
  10000    19870331       0.000000
  10000    19870430      -0.384615
  10000    19870531      -0.062500
  10000    19860228      -0.066667
  10001    19860331       0.020408
  10001    19860430       0.025200
  10001    19860531       0.009901
  10001    19860630      -0.009804
\end{verbatim}

This handles all the exceptions you can think of. The merged dataset is below:

\begin{verbatim}

   Obs      PERMNO        DATE            RET         lagret

    1       10000    19860228      -0.257143        .      
    2       10000    19860331       0.365385      -0.257143
    3       10000    19860430      -0.098592       0.365385
    4       10000    19860531      -0.222656      -0.098592
    5       10000    19860630      -0.005025      -0.222656
    6       10000    19860731        .            -0.005025
    7       10000    19860831      -0.615385        .      
    8       10000    19860930      -0.057143      -0.615385
    9       10000    19861031      -0.242424      -0.057143
   10       10000    19861130       0.060000      -0.242424
   11       10000    19861231      -0.377358       0.060000
   12       10000    19870131      -0.212121      -0.377358
   13       10000    19870228       0.000000      -0.212121
   14       10000    19870331      -0.384615       0.000000
   15       10000    19870430      -0.062500      -0.384615
   16       10000    19870531      -0.066667      -0.062500
   17       10000    19870630        .            -0.066667
   18       10001    19860228       0.020408        .      
   19       10001    19860331       0.025200       0.020408
   20       10001    19860430       0.009901       0.025200

\end{verbatim}

This will not work with daily data. First, Friday is not one day before Monday, but that can be handled by using \textit{weekday} as the incrementing unit in \textit{intnx}. Second, holidays make any systematization impossible.

For daily data, you need another method. Suppose I want  to find lag returns in the DSF file, that is, the return on the previous trading day.

The way I do it is to first create a dataset that contains, for every date in DSF, the date of the previous trading day. Then I merge this dataset into DSF by DATE, creating a dataset MAIN, which therefore has a LAGDATE associated with each DATE. Then I re-merge DSF with MAIN, but by PERMNO \textit{LAGDATE}, so that the values of RET being merged into MAIN are the lagged values.

First create a file with all the dates in the dataset, and create a lagdate:

\begin{verbatim}
  proc sort data=crsp.dsf(keep=date) out=dsfdates nodupkey;
     by date;
  run;

  data dsfdates;
     set dsfdates;
     lagdate=lag(date);
  run;

\end{verbatim}

Then merge that into DSF, by date:

\begin{verbatim}
  proc sort data=crsp.dsf(keep=permno date ret) out=dsf;
     by date;
  run;
				   
  data main;
     merge dsf dsfdates;
     by date;
  run;

\end{verbatim}

MAIN now has PERMNO DATE RET and LAGDATE. I need  to get LAGRET. To do this, I merge DSF into MAIN, but by PERMNO LAGDATE, not PERMNO DATE:

\begin{verbatim}

  data lagdsf(rename=(date=lagdate ret=lagret));
     set crsp.dsf(keep=permno date ret);
  run;

/*NOTE: DSF is already sorted by permno date*/

  data main;
     merge main(in=a) lagdsf;
     by permno lagdate;
  run;

\end{verbatim}

This is horribly inefficient, because you have to sort DSF by date, which takes 3 minutes at the best of times, but it serves to illustrate the point.

\subsection{Rolling regressions without macros}
\label{subsec:rollingwomacros}
\cite{boehmeretal} recommend using macros to run rolling regressions. While macros make impossible tasks possible, they aren't particularly efficient. I describe here a macro-independent way of running rolling regressions, and doing similar tasks. 

The task is to run rolling 24-month regressions of monthly stock excess returns on the Fama-French-Carhart factors. That is, at the end of each month, I need to:

\begin{itemize}
  \item Get the last 24 months of excess returns for each stock, checking that there are at least, say, 12 months of data available.
  \item Get the Fama-French-Carhart factors.
  \item Run rolling regressions
\end{itemize}

I first create a dataset containing ``rankdates'', which are the date identifiers for the rolling regression. A rankdate of 31Dec2001, for instance, uses data from 31Jan2000 to 31Dec2001, inclusive.

To do this, I first get the first and last date for each permno, and then create a complete list by filling in the in-between dates. I do this because if I were to just use the permno-date pairs available in MSF, if a return is missing for a particular month, then that month will not be a rankdate, even though it might be perfectly valid to make that date a rankdate.

\begin{verbatim}
  data firstandlastdates;
     set crsp.msf(keep=permno date);
     by permno; /*MSF is always sorted by permno date*/
     retain firstdate;
     date=intnx('month', date, 1)-1;
     if first.permno then firstdate=date;
     if last.permno then do;
         lastdate=date;
	 output;
     end;
  run;
  
  data permnosrankdates(rename=(date=rankdate));
      set firstandlastdates;
      date=firstdate;
      do while(date<=lastdate);
         output;
	 date=intnx('month', date+1, 1)-1;
      end;
  run;

\end{verbatim}

For each rankdate, I then get the list of the 24 dates from which that rankdate will use data.


\begin{verbatim}
  data permnosrankdates;
     set permnosrankdates;
     date=rankdate;
     i=1;
     do while(i<=24);
         output;
         date=intnx('month', date, 0)-1;
	 i=i+1;
     end;
  run;

\end{verbatim}

\textit{permnosrankdates} is a dataset that looks, in part, like this:

\begin{verbatim}
                Obs      PERMNO    rankdate         date     i

                  1       10000    19851231    31DEC1985     1
                  2       10000    19851231    30NOV1985     2
                  3       10000    19851231    31OCT1985     3
                  4       10000    19851231    30SEP1985     4
                  5       10000    19851231    31AUG1985     5
                  6       10000    19851231    31JUL1985     6
                  7       10000    19851231    30JUN1985     7
                  8       10000    19851231    31MAY1985     8
                  9       10000    19851231    30APR1985     9
                 10       10000    19851231    31MAR1985    10
                 11       10000    19851231    28FEB1985    11
                 12       10000    19851231    31JAN1985    12
                 13       10000    19851231    31DEC1984    13
                 14       10000    19851231    30NOV1984    14
                 15       10000    19851231    31OCT1984    15
                 16       10000    19851231    30SEP1984    16
                 17       10000    19851231    31AUG1984    17
                 18       10000    19851231    31JUL1984    18
                 19       10000    19851231    30JUN1984    19
                 20       10000    19851231    31MAY1984    20
                 21       10000    19851231    30APR1984    21
                 22       10000    19851231    31MAR1984    22
                 23       10000    19851231    29FEB1984    23
                 24       10000    19851231    31JAN1984    24
                 25       10000    19860131    31JAN1986     1
                 26       10000    19860131    31DEC1985     2
                 27       10000    19860131    30NOV1985     3
                 28       10000    19860131    31OCT1985     4
                 29       10000    19860131    30SEP1985     5
                 30       10000    19860131    31AUG1985     6
\end{verbatim}

We don't need to keep i, but I kept it for clarity.

Once we have this, all we need to do is merge it with the factors and the returns:

\begin{verbatim}
  data ff;
      set ff.factors_monthly(keep=date rf smb hml umd mktrf);
      date=intnx('month', date, 1)-1;      
  run;

  proc sort data=permnosrankdates;
      by date permno;

  data permnosrankdates;
      merge permnosrankdates(in=a) ff(in=b);
      by date;
      if a and b;
  run;

  data msf;
     set crsp.msf(keep=permno date ret);
     where ret is not missing;
     date=intnx('month', date, 1)-1;
  run;

  proc sort data=msf;
      by date permno;
  run;
  
  /*permnosrankdates is already sorted*/

  data permnosrankdates;
      merge permnosrankdates(in=a) msf(in=b);
      by date permno;
      if a and b;
  run;

\end{verbatim}

Notice that I merged by \textit{date}, not rankdate. 

And now all that remains is to calculate excess returns and run the regressions:

\begin{verbatim}
  data permnosrankdates;
     set permnosrankdates;
     exret=ret-rf;
  run;

  proc sort data=permnosrankdates;
      by permno rankdate;

  proc reg data=permnosrankdates outest=est edf;
      by permno rankdate;
      model exret=mktrf smb hml umd;
  run;

\end{verbatim}

Notice I run proc reg by \textit{rankdate}, not date. 

Running proc print on EST yields the following output:


\begin{verbatim}

   Obs   PERMNO rankdate _MODEL_ _TYPE_ _DEPVAR_  _RMSE_ Intercept    mktrf

     1    10000 19860228 MODEL1  PARMS   exret    .        -0.2624    0.000
     2    10000 19860331 MODEL1  PARMS   exret    .         1.9027  -32.219
     3    10000 19860430 MODEL1  PARMS   exret    .         1.4644  -31.110
     4    10000 19860531 MODEL1  PARMS   exret    .         4.3923    2.102
     5    10000 19860630 MODEL1  PARMS   exret    .       -14.0666 -191.074
     6    10000 19860731 MODEL1  PARMS   exret   0.48716   -0.1872   -4.129
     7    10000 19860831 MODEL1  PARMS   exret   0.34479   -0.1559   -2.784
     8    10000 19860930 MODEL1  PARMS   exret   0.29117   -0.1216   -0.492
     9    10000 19861031 MODEL1  PARMS   exret   0.25216   -0.1214   -0.499
    10    10000 19861130 MODEL1  PARMS   exret   0.25809   -0.0767   -1.790
    11    10000 19861231 MODEL1  PARMS   exret   0.27266   -0.1228   -0.709
    12    10000 19870131 MODEL1  PARMS   exret   0.25251   -0.1217   -0.665
    13    10000 19870228 MODEL1  PARMS   exret   0.23828   -0.1090   -0.784
    14    10000 19870331 MODEL1  PARMS   exret   0.24214   -0.1270   -1.872
    15    10000 19870430 MODEL1  PARMS   exret   0.22978   -0.1254   -1.942
    16    10000 19870531 MODEL1  PARMS   exret   0.22052   -0.1194   -1.983
    17    10000 19870630 MODEL1  PARMS   exret   0.22052   -0.1194   -1.983

   Obs      smb        hml       umd   exret   _IN_   _P_   _EDF_    _RSQ_

     1    0.000       0.00     0.000     -1      0     1       0     .     
     2    0.000       0.00     0.000     -1      1     2       0    1.00000
     3  -71.326       0.00     0.000     -1      2     3       0    1.00000
     4  340.707     477.88     0.000     -1      3     4       0    1.00000
     5 -884.257   -1322.51   506.315     -1      4     5       0    1.00000
     6  -13.672     -16.38     7.106     -1      4     5       1    0.05020
     7   -8.529     -10.71     5.470     -1      4     5       2    0.54571
     8    7.327       3.24     4.754     -1      4     5       3    0.51848
     9    7.303       3.22     4.751     -1      4     5       4    0.52996
    10    3.312      -0.84     3.657     -1      4     5       5    0.42145
    11    4.391       0.97     3.748     -1      4     5       6    0.30088
    12    4.378       0.91     3.725     -1      4     5       7    0.30569
    13    4.342      -0.04     3.353     -1      4     5       8    0.31433
    14    0.837      -3.20     2.548     -1      4     5       9    0.26778
    15    0.669      -3.35     2.531     -1      4     5      10    0.27495
    16    0.604      -3.41     2.454     -1      4     5      11    0.27148
    17    0.604      -3.41     2.454     -1      4     5      11    0.27148

\end{verbatim}

We can tell how many observations we have for each date by looking at the \_EDF\_: the error degrees of freedom. We asked for this by including \textit{edf} in the proc model statement. If we want to keep only regressions for which there were 12 observations or more, we keep those where \_EDF\_>=7.

EST is the rolling regression output dataset.


This technique has wide application: rolling regressions, obviously, but also rolling standard deviations, moving averages and whatnot.

\section{Missing values}
   In procedures: means ( FREQ , N), freq
   In additions: the sum() function


\section{Code debugging}
\subsection{Run on a small subset of observations, making sure your code doesn't overwrite anything}

There are two ways to run your code on a small subset of observations this. One is to identify the largest dataset in your program and to use the ``obs='' dataset option. 

   \begin{verbatim}
     data msf;
        set crsp.msf(keep=permno date ret obs=2000);
        date=intnx('month', date, 1)-1;
     run;
   \end{verbatim}
   
   The other is to set the global ``obs='' option. This is done by putting the following statement in open code: i.e., not within a data or proc step.
   \begin{verbatim}
     options obs=2000;
   \end{verbatim}
   What this does is to read a maximum of 2000 observations from any dataset the program encounters. It's quick to put this at the head of your program and run it to check for coding errors. This will find, for instance, variables you have inadvertently left out and referred to later, but other errors may not show up. For example, if observations in a data or proc step that uses by-variables were not properly sorted, you might not know because the particular subset that you run the code with happens to be properly sorted.
   
   To prevent your code from overwriting datasets while you're debugging it, use the ``noreplace'' option, which prevents overwriting of existing datasets:
   \begin{verbatim}
     options obs=2000 noreplace;
   \end{verbatim}
				   
\subsection{See what the macro sees}
   \label{subsec:seewhatmacrosees}
   Useful options (placed in open code) to run macro code with are 
   
   \begin{verbatim}
     options symbolgen mprint mlogic;
   \end{verbatim}
   
   which cause the program to print the SAS code that the macro generates to the log. You are apparently not supposed to use these in actual runs: they slow the program down. To turn these off say 

   \begin{verbatim}
     options nosymbolgen nomprint nomlogic;
   \end{verbatim}
   
   \subsection{Set msglevel=i}
   \label{subsec:msgleveli}
   \subsection{Print variables to the log within the data step}
   
   At any point in the data step you can say 
   \begin{verbatim}
     put <variable-list>
   \end{verbatim}
   and the observations of the variables in variable-list will be written to the log. You can also use this in a conditional:
   \begin{verbatim}
     data msf;
        set crsp.msf(obs=200);
        if permno>10006 then put permno date ret;
     run;
   \end{verbatim}
   The ``obs=200'' is so that my log file does not get huge.

   \subsection{Using the DEBUG option}

\section{Macros}
\subsection{Macro variables}
\subsubsection{Subsetting observations from large datasets efficiently}
In the section on efficiency (Section \ref{sec:effic}), I advocate using \textit{where} statements to subset datasets before reading them in. For instance, say 

   \begin{verbatim}
     data dsf;
        set crsp.dsf(keep=permno date ret);
	where permno in(10001, 10002, 10003);
     run;
   \end{verbatim}
 
   instead of 
   \begin{verbatim}
     data dsf;
        set crsp.dsf(keep=permno date ret);
	if permno=10001 or permno=10002 or permno= 10003;
     run;
   \end{verbatim}

   What happens when you do not know in advance what permnos you want to keep? Or if you do know, but the list is a headache to type out?
   
   In that case, you can replace the contents of the where with a macro variable. Suppose some previous steps produce a dataset, named KEEPTHESE that contains the permnos you want to keep: 
   \begin{verbatim}
   Obs      PERMNO

    1       10000
    2       10006
    3       10009
    4       10021
   \end{verbatim}

Given this dataset, I want to subset CRSP.DSF, keeping only the permnos listed in KEEPTHESE. To do this, I first run the following data step, which creates several macro variables.

   \begin{verbatim}
     data _null_;
        set keepthese nobs=nobs;
	 if _n_ =1 then call symput("nobs", nobs);
         call symput("permname"||trim(left(put(_N_,4.))), permno);
     run;
     \end{verbatim}
   The first line after the SET statement creates a macro variable named NOBS, which contains the number of observations in KEEPTHESE. The second line creates a series of macro variables named \textit{permname1, permname2, permname3, ...} containing the permnos in the order in which they appeared in KEEPTHESE. Now inside a macro, I concatenate all these macro variables.
   \begin{verbatim}
     %macro justconcatenate;
        %let listofpermnos=&permname1;
        %do i =2 %to &nobs;
           %let listofpermnos =%sysfunc(catx(%str(,),&listofpermnos, "%trim(&&permname&i)"));
        %end;
     %let listofpermnos=(&listofpermnos);
     %mend;
     %justconcatenate;
   \end{verbatim}
   This macro just creates a macro variable named LISTOFPERMNOS whose value is set to the contents of the macro variable PERMNAME1. I then join all the remaining PERMNAMEs to this LISTOFPERMNOS with commas as delimiters.

   Finally, I can use the macro variable so created:
   \begin{verbatim}
     data dsf;
        set crsp.dsf(keep=permno date ret);
	where permno in &listofpermnos;
     run;
   \end{verbatim}
   
   This might seem like a lot of bother for nothing, but supposing you are work with a large dataset, like TAQ, and you need to keep observations with symbols in a dataset like KEEPTHESE. Sorting and merging is not an option. This method works well, as would hashes(see section \ref{sec:merges}). I have not compared this method against a hash.

\subsection{Loops}
Macro loops allow you to \%DO something for an index in some range of integers. For instance, you can write
   \begin{verbatim}
     %macro justaloop;
        %do i =1 %to 200;
             <some code>
        %end;
     %justaloop;
   \end{verbatim}
and \textit{some code} will be run for all values of \&j.

\subsubsection{Counters are dates}

\label{subsubsec:varisdate}
A useful thing would be to run a macro loop with a changing date. It would be great if I could say 
   \begin{verbatim}
     %macro justaloop;
        %do date ='31Jan1980'd %to '31Jan2000'd;
             <some code>
        %end;
     %mend;
     %justaloop;
   \end{verbatim}
This is invalid because only integer values can be supplied as index values. SAS thinks that you're trying to loop starting with \&date set to be the \textit{string} '31Jan1980'd. 

One option is to get the numeric version of the starting and ending dates and then use those in the loop:

   \begin{verbatim}
     %let startdate= '31Jan1980'd;
     %let enddate= '31Jan2000'd;
     
     %macro justaloop;
     %let startdate=%sysfunc(putn(&startdate, 8.));
     %let enddate=%sysfunc(putn(&enddate, 8.));
        %do date =&startdate %to &enddate;
             <some code>
        %end;
     %mend;
     %justaloop;
   \end{verbatim}

   The statement

\begin{verbatim}
   %let startdate=%sysfunc(putn(&startdate, 8.));
\end{verbatim}

converts the contents of \&STARTDATE from \textit{'31Jan1980'd} to \textit{7335}.The \%sysfunc macro function allows you to use DATA step functions in macro code. In this case, it allows me to use the PUTN function.

Using a \%put statement to tell me how \&STARTDATE changes (with options symbolgen on: see Section~\ref{subsec:seewhatmacrosees}): 

\begin{verbatim}
  %let startdate= '31Jan1980'd;
  %let startdate=%sysfunc(putn(&startdate, 8.));
  %put startdate is &startdate;
\end{verbatim}

the following is written to the log:

\begin{verbatim}
              %let startdate= '31Jan1980'd;
        
              %let startdate=%sysfunc(putn(&startdate, 8.));
SYMBOLGEN:  Macro variable STARTDATE resolves to '31Jan1980'd
         
              %put startdate is &startdate;
SYMBOLGEN:  Macro variable STARTDATE resolves to 7335
startdate is 7335
\end{verbatim}

The loop above does whatever \textit{some code} does for each \textit{day} between the two dates. However, in this loop I cannot adjust the interval by which the date counter is incremented. For instance, I might want to do something for each end-of-month between startdate and enddate. 

To do \textit{that}, I don't increment the date by 1, instead I increment it manually using a \%SYSFUNC call to INTNX:
   \begin{verbatim}
     %let startdate= '31Jan1980'd;
     %let enddate= '31Jan2000'd;
     
     %macro justaloop;
     %let startdate=%sysfunc(putn(&startdate, 8.));
     %let enddate=%sysfunc(putn(&enddate, 8.));

     %let date=&startdate;
      %do %while(&date <= &enddate);
             <some code>
             %let date=%eval(%sysfunc(intnx(month, &date+1, 1))-1);
        %end;
     %mend;
     %justaloop;
   \end{verbatim}

Another option is to simply do it by year and month and use the mdy() function to convert it into a date as needed:

   \begin{verbatim}
     %macro justaloop;
     %do year=1963 %to 2007;
         %do month=1 %to 12;
            
            %let begofmonthdate=%sysfunc(mdy(&month, 1, &year));
            %let endofmonthdate=%eval(%sysfunc(intnx(month, &begofmonthdate, 1))-1);
             <some code>
         %end;
     %end;
     %mend;
     %justaloop;
   \end{verbatim}
   
This is not very flexible.

\subsubsection{	\%FOR variable in \&LIST}

I often want to provide a list of variables, say, to a macro and have it run for each of them, instead of cluttering up my code with several invocations of the same macro for each of the variables. 

Suppose I want to run the macro $\%calculate\_means$ for three variables, $ret$, $retx$ and $shrout$. I could say:

\begin{verbatim}
  %calculate_means(ret);
  %calculate_means(retx);
  %calculate_means(shrout);
\end{verbatim}

Or I could write a foreach macro:

\begin{verbatim}
  %macro foreach(listofvariables);

  %let i=1;
  %do %while(%scan(&listofvariables, &i, ' ')>0);
        %let thevar=%scan(&listofvariables, &i, ' ');
  
        %put We have the variable &thevar;

        %calculate_means(&thevar);

        %let i=%eval(&i+1);
        %end;
    %mend;

\end{verbatim}

And call it with

\begin{verbatim}

%foreach(ret retx shrout);

\end{verbatim}

\subsubsection{Subsetting observations from large datasets efficiently}
Suppose you want to calculate the average correlation between stocks in each month in your sample. Calculating correlations means you will have a dataset with N-squared elements, where N is the number of stocks in that month. To do this, you write a macro loop (see section \ref{subsubsec:varisdate}):

   \begin{verbatim}
     %let startdate= '31Jan1980'd;
     %let enddate= '31Jan2000'd;
     
     %macro justaloop;
     %let startdate=%sysfunc(putn(&startdate, 8.));
     %let enddate=%sysfunc(putn(&enddate, 8.));

     %let date=&startdate;
      %do %while(&date <= &enddate);

            data tempdsf;
               set crsp.dsf;
	       where intnx('month', date, 1)-1 = &date;
	    run;
	    
	    <code to calculate correlations>

            %let date=%eval(%sysfunc(intnx(month, &date+1, 1))-1);
        %end;
     %mend;
     %justaloop;
   \end{verbatim}

   This is inefficient because it takes long to subset DSF each time. Instead, do the following:
\begin{enumerate}
\item Outside the loop, sort DSF by date
\item Outside the loop, create an ``index'' dataset that tells you for each month where the data for that month begins and ends in the sorted DSF
\item Within the loop, use FIRSTOBS= and OBS= to read only the required observations
\end{enumerate}

Sorting DSF and creating an index are simple:

\begin{verbatim}
  
  proc sort data=crsp.dsf out=dsf;
    where date>=&startdate and date<=&enddate;
    by date;
  run;
    
  data dsfindex(keep=date firstobs lastobs);
     set dsf;
     by date;
     retain firstobs;
     if first.date then do;
           firstobs=_n_;
        end;
     if last.date then do;
           lastobs=_n_;
           output;
        end;
    run;
    
\end{verbatim}

DSFINDEX now contains, for each date, the observation numbers at which the observations for that date begin and end.

Within the loop, you then say:

   \begin{verbatim}
     data _null_;
        set dsfindex;
	where date=&date;
	call symput('firstobs', firstobs);
        call symput('lastobs', lastobs);
     run;

     data tempdsf;
        set dsf(firstobs=&firstobs obs=&lastobs);
     run;

   \end{verbatim}

The DATA \_NULL\_ statement just means that I am not creating a new dataset when I subset DSFINDEX. The only reason I am opening DSFINDEX is to get the values of FIRSTOBS and LASTOBS into the corresponding macro variables.

I then subset DSF to get TEMPDSF, keeping only the observations numbered between \&FIRSTOBS and \&LASTOBS. Note that the \textit{obs=} statement tells you the last observation that will be read, \textit{not} the number of observations to read.

TEMPDSF now contains all the observations for which DATE equals \&DATE. The savings here are enormous: this takes virtually no time.

The complete code thus reads:

   \begin{verbatim}
  %let startdate= '31Jan1980'd;
  %let enddate= '31Jan2000'd;

  proc sort data=crsp.dsf out=dsf;
      where date>=&startdate and date<=&enddate;
      by date;

  data dsfindex(keep=date firstobs lastobs);
     set dsf;
     by date;
     retain firstobs;
     if first.date then do;
           firstobs=_n_;
        end;
     if last.date then do;
           lastobs=_n_;
           output;
        end;
  run;
  %macro justaloop;
    %let startdate=%sysfunc(putn(&startdate, 8.));
    %let enddate=%sysfunc(putn(&enddate, 8.));
    
    %let date=&startdate;
      %do %while(&date <= &enddate);
    

            data _null_;
               set dsfindex;
	       where date=&date;
	       call symput('firstobs', firstobs);
               call symput('lastobs', lastobs);
	    run;

            data tempdsf;
               set dsf(firstobs=&firstobs obs=&lastobs);
             run;
	    
	    <code to calculate correlations>

           %let date=%eval(%sysfunc(intnx(month, &date+1, 1))-1);
      %end;
   %mend;
   %justaloop;
   \end{verbatim}
				   
\subsection{Writing frequently-used code as macros}
\label{subsec:frequsedasmac}
Several operations are so standard that if you write them up as macros and save them in a macro file, you will be spared a lot of typing. You can then automatically include this macro file in all your SAS programs -  see the section on autoexec.sas, Section \ref{subsec:autoexec}.

An example is the merge algorithm. Merging always proceeds in the same way. You are given two datasets and the by-variables, you sort them by the by-variables and then merge them. I wrote up a ``standard merge'' macro named \%stdmerge:

  \begin{verbatim}
%macro stdmerge(inds1, inds2, byvar, outds);
    proc sort data=&inds1 nothreads;
       by &byvar;
    run;

    proc sort data=&inds2 nothreads;
       by &byvar;
    run;    
    data &outds;
       merge &inds1(in=a) &inds2(in=b);
       by &byvar;
       if a and b;
    run;
%mend;
   \end{verbatim}

In the SAS programs I write, when I want to merge two datasets, all I need to do is say 

  \begin{verbatim}
    %stdmerge(firstdataset, seconddataset, by-variable-list, outputdataset)
   \end{verbatim}

instead of laboriously typing out the whole thing every time. This clears up an enormous amount of clutter from my SAS programs, making them much more readable. 

\subsubsection{Merging macros}
\%stdmerge you have already met. The others just have varying ``if'' conditions.
\begin{itemize}
\item{\%kbmerge}: ``Keep both'' merge keeps an observation if it is in either of the input datasets. In addition, it creates variables that tell you which dataset an observation came from:
  \begin{verbatim}
%macro kbmerge(inds1, inds2, byvar, outds);
    proc sort data=&inds1 nothreads;
			      by &byvar;
        
    proc sort data=&inds2 nothreads;
        by &byvar;
    
    data &outds;
        merge &inds1(in=a) &inds2(in=b);
        by &byvar;
        if a or b;
        in&inds1=a;
        in&inds2=b;
    run;
%mend;
   \end{verbatim}

\item{\%kfmerge}: ``keep first'' merge keeps only observations from the first dataset. This is just \%stdmerge with the ``if'' line replaced.
				   
  \begin{verbatim}
%macro kfmerge(inds1, inds2, byvar, outds);
    proc sort data=&inds1 nothreads;
        by &byvar;
        
    proc sort data=&inds2 nothreads;
        by &byvar;
    
    data &outds;
        merge &inds1(in=a) &inds2(in=b);
        by &byvar;
        if a;
    run;
%mend;

   \end{verbatim}

\item{\%rmsecmerge}: ``remove second'' merge removes the observations from the first dataset that occur in the second. The only difference again is the if line:
  \begin{verbatim}
%macro rmsecmerge(inds1, inds2, byvar, outds);
    proc sort data=&inds1 nothreads;
        by &byvar;
        
    proc sort data=&inds2 nothreads;
        by &byvar;
    
    data &outds;
        merge &inds1(in=a) &inds2(in=b);
        by &byvar;
        if not b;
    run;
%mend;

   \end{verbatim}
\end{itemize}
\subsubsection{Hash-merging without having to type}	
   \label{subsubsec:hashmergemacro}
See section~\ref{subsubsec:mergenosort} for how to use hashes to efficiently merge small datasets into large ones. Here I describe a macro that does it without typing.

This macro consists of three separate macros. The two small ones are called \%enquote and \%sepbycommas. They accept space-separated lists of variable names and print out, in the first case, that list with double-quotes around each name, separated by commas, and, in the second case, just the variable names separated by commas. If you provide

\begin{verbatim}
permno date ret
\end{verbatim}

to the first macro, it will return

\begin{verbatim}
``permno'',''date'',''ret''
\end{verbatim}

And the second will return 

\begin{verbatim}
permno,date,ret
\end{verbatim}

The macro that does the actual work, \%hashmerge, just prints out the data step that does the hash merge. 

\begin{verbatim}
%macro sepbycomma(varlist);             
    %let delim=%str( );
    
    %let outputvar=%qscan(&varlist,1,&delim);
    %let i=2;
    %do %while(%length(%qscan(&varlist,&i,&delim)) GT 0);
        %let varhere=%qscan(&varlist,&i,&delim);
        %let outputvar=&outputvar,&varhere;
        %let i=%eval(&i + 1);
        
        %end;
    &outputvar
    %mend;

%macro enquote(varlist);             
    %let delim=%str( );
    
    %let outputvar=''%qscan(&varlist,1,&delim)'';
    %let i=2;
    %do %while(%length(%qscan(&varlist,&i,&delim)) GT 0);
        %let varhere=%qscan(&varlist,&i,&delim);
        %let outputvar=&outputvar,''&varhere'';
        %let i=%eval(&i + 1);
        
        %end;
    &outputvar
    %mend;

            
%macro hashmerge(largeds, smallds, byvars, extravars, outds);
    data &outds;
        call missing(%sepbycomma(&extravars));
        if _n_=1 then do;
         declare hash h(dataset:''&smallds'');
         h.defineKey(%enquote(&byvars));
         h.defineData(%enquote(&extravars));
         h.defineDone();
         end;
     set &largeds;
     if h.find()=0;
    run;

    %mend;


\end{verbatim}


\subsubsection{Checking for duplicate by-variable combinations}	
Before a merge, I frequently want to check that there is only one instance of every by-variable combination in one or both of my datasets. If I am doing what I think is a one-to-one merge, then obviously each dataset should contain only one instance of every by-variable combination. If I am doing a many-to-one merge, then one dataset should have only one instance of every by-variable combination. 
				   
(Alternatively, suppose I am merging two datasets using a fuzzy merge variable. After the merge is done, I want to see which observations in one dataset were matched to a single observation in the other, and which were matched to two or more.) 

Suppose I have a mutual fund holdings dataset that only contains, as stock information, the ticker of the stock. That is, it contains

\begin{verbatim}
  CRSP_FUNDNO DATE STOCKTICKER SHARES_HELD
\end{verbatim}
I need to get the permno for these stocks. To do this, I first create a permno-date-ticker file as described in Section  \ref{subsec:pwd2}. 
\begin{verbatim}
  data stocknames(keep=permno date ticker);
        set crsp.stocknames(keep=namedt nameenddt permno ticker);
	format date date9.;
	where namedt is not missing;
	date=intnx('month', namedt, 1)-1; 
	do while(date<=nameenddt);
	   output;
	   date=intnx('month', date+1, 1)-1;
	   end;
     run;
   \end{verbatim}

The first thing to do is to check that this dataset has, at each date, only 
\begin{enumerate}
\item One ticker for each permno and
\item One permno for each ticker.
\end{enumerate}

To check that each ticker has only one permno, I do the following:

\begin{verbatim}
proc sort data=stocknames;
    by date ticker permno;
run;

data doubles singles;
    set stocknames;
    by date ticker permno;
    if not (first.ticker and last.ticker) then output doubles;
    else output singles;
run;

proc print data=doubles(obs=20);
run;

\end{verbatim}

The data step could do with some explaining. Since stocknames is sorted by DATE TICKER PERMNO, if each ticker linked to only one permno on each date, it should be the case that each date-ticker combination has only one entry in the dataset. If it does not, then that date-ticker combination links to more than one permno.

This produces output like this:
\begin{verbatim}
                        Obs    PERMNO    ticker         date

                        1     29938     BFD      31JUL1962
                        2     29946     BFD      31JUL1962
                        3     27414     BO       31JUL1962
                        4     27561     BO       31JUL1962
                        5     29866     BQ       31JUL1962
                        6     29874     BQ       31JUL1962
                        7     30891     CDL      31JUL1962
                        8     30904     CDL      31JUL1962
                        9     31093     CLI      31JUL1962
                       10     31106     CLI      31JUL1962
                       11     32854     HG       31JUL1962
                       12     32862     HG       31JUL1962
                       13     32934     HUB      31JUL1962
                       14     32942     HUB      31JUL1962
                       15     35553     PDL      31JUL1962
                       16     35561     PDL      31JUL1962
                       17     35115     PKR      31JUL1962
                       18     35123     PKR      31JUL1962
                       19     22517     PPL      31JUL1962
                       20     35051     PPL      31JUL1962
				   
\end{verbatim}
where, as you can see, the doubles dataset contains the desired elements. I then use the singles dataset, which has only one permno for each ticker, in further tests, notably, seeing whether each permno matches to only one ticker.

This process is easily automated in a macro:

\begin{verbatim}
%macro separatedups(inds, byvar, dupbyvar);

    proc sort data=&inds;
        by &byvar;
    run;

    data zzsingles zzdoubles;
        set &inds;
        by &byvar;
        if not(first.&dupbyvar and last.&dupbyvar) then output zzdoubles;
        else output zzsingles;
    run;

    %let doubsnobs=0;
    
    data _null_;
        set zzdoubles nobs=nobs;
        if _n_=1 then call symput("doubsnobs", trim(left(nobs)));
    run;

    %if "&doubsnobs"="0" %then %do;    
        %end;
    %else %do;
        %put WARNING: &doubsnobs observations in doubles;
        %end;
    %mend separatedups;

\end{verbatim}
And called with:

\begin{verbatim}
  %separatedups(stocknames, date ticker permno, ticker);
\end{verbatim}

I can then check both ways by saying:

\begin{verbatim}
  %separatedups(stocknames, date ticker permno, ticker);

  data stocknames;
     set zzsingles;
  run;

  %separatedups(stocknames, date permno ticker, permno);

  data stocknames;
     set zzsingles;
  run;

\end{verbatim}

In the first call to \%separatedups, there are multiple permnos for each ticker, so zzdoubles is not empty and the following message is written to the log:

\begin{verbatim}
WARNING: 35222 observations in doubles
\end{verbatim}

In the second call, zzdoubles is empty: each permno matches to only one ticker. 

\subsubsection{Getting the number of observations in a dataset}

We can do this with a call to \texttt{symput}, but we can also do this the following way:

\begin{verbatim}
%macro obsnvars(ds);
    %global zznvars zznobs;
    %let dset=&ds;
    
 /* Open data set passed as the macro parameter */
    %let dsid = %sysfunc(open(&dset));
    
 /* If the data set exists, then grab the number of observations and variables */
 /* then close the data set                                                    */
 %if &dsid %then
     %do;
     %let zznobs =%sysfunc(attrn(&dsid,nobs));
     %let zznvars=%sysfunc(attrn(&dsid,nvars));
     %let zzrc = %sysfunc(close(&dsid));
     %end;
 
 /* Otherwise, write a message that the data set could not be opened */
     %else %put ERROR: open for data set &dset failed - %sysfunc(sysmsg());

     &zznobs

    %mend;

\end{verbatim}

This creates two global macro variables, \texttt{zznobs}, the number of observations, and \texttt{zznvars}, the number of variables. It also prints out the number of observations, so this macro becomes almost a function. You can even do things like this:

\begin{verbatim}
  data msf;
  set crsp.msf(obs=%obsnvars(crsp.msf));
  run;
\end{verbatim}
instead of the more standard:
\begin{verbatim}
%let reqdobs=%obsnvars(crsp.msf);
  data msf;
  set crsp.msf(obs=&reqdobs);
  run;
\end{verbatim}

\subsubsection{Sundry macros}	
\begin{itemize}
\item Printing: 

When I debug, I like to print out small subsets of my datasets at each point. Typing 
\begin{verbatim}
proc print data=datasetname(obs=20);
   title ``datasetname'';
run;
title;
\end{verbatim}
just doesn't cut it. The \%stdprint macro allows me to avoid this
\begin{verbatim}
%macro stdprint(inds, sopt);
   proc print data=&inds(obs=20);
        title "&inds : &sopt";
    run;
    title;
    %mend;
\end{verbatim}
I call this by saying 

\begin{verbatim}
%stdprint(datasetname);
\end{verbatim}

This prints the first 20 observations, together with the dataset name as title. It also allows me to have an optional extra subtitle (\&sopt in the macro definition), so I can distinguish different printings of the same dataset.

\%comprint does the exact same thing, except that it prints the whole dataset. It is \%stdprint without the ``obs=20''.

\item Cleaning up the WORK directory: 

If you're dealing with large datasets and you have a space constraint, you might want to delete each dataset after you're done with it, so that space in your WORK directory is freed.

One way to do this is to say

\begin{verbatim}
proc datasets lib=work nolist;
    delete datasetname;
quit;
\end{verbatim}

The nolist option stops proc datasets from printing a list of members of the work directory to the log file. 

This can be replaced by:
\begin{verbatim}
%macro dsdel(inds);
    proc datasets lib=work nolist;
        delete &inds;
        quit;
    %mend;
\end{verbatim}

And the one-liner in open code
\begin{verbatim}
%dsdel(useless_dataset);
\end{verbatim}

\item{Checking datasets for ``wrong'' observations}

I find it useful to include built-in checks in my programs, so that I can say ``Report an error if dataset A has any observations for which variable Y is nonzero.'' This is easy to do:

\begin{verbatim}
%macro nonemptysubsetiswrong(dsn,wherecond);

    %let oldsyslast=&syslast;
    data _DATA_;
        set &dsn;
        where &wherecond;
    run;
    %let temp_dataset=&syslast;
    
    %if %obsnvars(&temp_dataset)>0 %then %do;
        %put ERROR: applying &wherecond to &dsn results in observations in subset;
        %comprint(&temp_dataset, Dataset &dsn should be empty where &wherecond);
        %end;

    %dsdel(&temp_dataset);    
    %let syslast=&oldsyslast;
    %mend;

\end{verbatim}

Call with \texttt{%nonemptysubsetiswrong(crsp.msf,prc<0);}. This will print a note to the log: ``ERROR: applying prc<0 to crsp.msf results in observations in subset'', and also prints out the ``faulty'' observations to the lst file. 


  
\end{itemize}

\subsubsection{Regressions}
\label{subsubsec:macros_reg}
Often, in preliminary analysis, I want to run simple regressions and have printed output that isn't the reams and reams of inessential statistics that SAS produces when I run proc reg. To do this, I use the following macro:

\begin{verbatim}
%macro regme(inds, depvar, indvars, subt);

    proc reg data=&inds;
        model &depvar = &indvars;
        ods select ParameterEstimates;
	title ``&subt'';
    run;

%mend;
\end{verbatim}

And now I can write, for instance,

\begin{verbatim}
%regme(crsp.msf, ret, retx, Regression of return on return without dividends);
\end{verbatim}

which produces the beautifully simple output

\begin{verbatim}
    Regression of return on return without dividends  
											   
  Obs    Variable        Estimate     tValue     Probt
											   
   1     Intercept        0.00192     467.15    <.0001
   2     RETX             0.99877    41951.9    <.0001
\end{verbatim}
which is all I am really interested in anyway.

\%regmewhite

\section{The DOW loop}
\label{sec:thedowloop}
The DOW loop is a construct in which observations are read from an input dataset via a set statement, and the set statement is inside a do-loop.

For instance, suppose I wanted to find the mean return for each permno in the MSF file. I could do this in two ways.

The obvious way is:

\begin{verbatim}
proc means data=crsp.msf noprint;
   by permno;
   var ret;
   output out=means mean=meanret;
run;

\end{verbatim}
The DOW-loop way is:

\begin{verbatim}
data means;
   count=0;
   sum=0;
   do until (last.permno);
      set crsp.msf;
      where ret is not missing;
      by permno;
      sum=sum+ret;
      count=count+1;
   end;
   meanret=sum/count;
   output means;
run;

\end{verbatim}

What's going on here? Briefly, the do-loop iterates over each permno's returns, exiting when the last observation for that permno has been reached. When this happens,meanret is calculated and output.

This might seem like a fancy construct that has no obvious practical use, but its utility is enormous, particularly when you want to merge two datasets and then calculate summary statistics on the merged dataset, as will be explained in section \ref{subsec:pmp}.

For further reading, see \cite{dsdow}.

\section{Merges}

\label{sec:merges}
\subsection{Merging a dataset with a single observation into another dataset}
   A common task is merging a dataset which has summary statistics into the dataset that contained the original data from which those summary statistics were calculated.

   For instance, I may want to calculate the demeaned value of a variable. To do this, I first use proc means to calculate the mean of that variable and then merge proc means' output dataset into the parent dataset. 

   I describe a construct that does this efficiently. 

   Suppose the parent dataset is crsp.msf, and I wish to calculate the demeaned value of returns.

   I first find the mean value using proc means:

\begin{verbatim}

proc means data=crsp.msf noprint;  
    var ret;
output out=summarystats(drop=_type_ _freq_) mean=meanret;
run;
   \end{verbatim}
SUMMARYSTATS has one variable named meanret, and one observation. To merge this back into msf, I say:

\begin{verbatim}

data msf;
   if _n_ =1 then set summarystats;
   set msf;
run;
   \end{verbatim}
This creates the additional variable meanret in msf and copies its one value to all observations in msf.

Why does this work? It's a complicated answer. This relies on two things, the implicit RETAIN in every SET statement and the behaviour of SAS with respect to the endings of input datasets. These are fairly complicated and not essential, so I shall not go into them here. Interested readers may look at Sections \ref{subsec:whatreally} and \ref{subsec:thefascdoloop} .

\subsection{Merging without sorting: the DATA step HASH object}
   \label{subsubsec:mergenosort}
I often have to merge a small dataset into a much larger dataset. If the larger dataset is not appropriately sorted, this is a process that consumes time and resources. An option is to use the DATA step HASH object.

The HASH object allows you to load a ``small'' dataset into RAM as a hash. A hash, or associative array, is a list indexed by a key. For instance, for the MSF dataset, the key variables are permno and date -- which means that for a particular permno-date combination, the MSF dataset contains one observation, with the data being return, price, shares outstanding and so on. Given a permno-date combination, I could find the data for that combination by opening the MSF dataset and going through it until I hit the appropriate value. What a hash does is to allow you to \textit{instantly} access the data for any specified permno-date combination \textit{without going through the entire dataset}.

How do you create and use the hash? An example is probably the best way to explain.

In this example, I want to merge the monthly-frequency Fama-French factors with the MSF dataset, so I can run regressions to calculate betas.
\begin{verbatim}
data msf;
    set crsp.msf(keep=permno date ret);
    where ret is not missing;
    date=intnx('month', date, 1)-1;
run;    

data ff;
    set ff.factors_monthly(keep=date mktrf smb hml umd rf);
    date=intnx('month', date, 1)-1;
run;
  
\end{verbatim}

All standard so far. Observe that I make the dates in both datasets end-of-month. 

Now I define and use the hash.

\begin{verbatim}
data msf;
    if _n_=1 then do;
        declare hash h(dataset:'ff');
        h.defineKey('date');
        h.defineData('mktrf', 'smb', 'hml', 'umd', 'rf');
	call missing(mktrf, smb, hml, umd, rf);
        h.defineDone();
	end;
    set msf;
    if h.find() = 0 then output;
run;    

\end{verbatim}

What this does is:
\begin{enumerate}
\item On the first observation (\_N\_=1), before any data is read from the SET MSF statement, a hash named H is defined. This hash consists of data from the dataset FF. The hash key, which will be used to perform lookups, is defined to be DATE. The hash data is defined to be MKTRF, SMB, HML, UMD, RF. The CALL MISSING statement sets the value of these variables in the output dataset to missing initially. If you omit this, SAS will complain that the variable are not defined. 
\item Then the first observation is read from the SET MSF statement, loading values of DATE, PERMNO, and RET. Suppose these values are 10000, 19860228, and -0.257143. The H.FIND() statement immediately below the SET statement looks at the values of the KEY variables currently being processed. In this case, the KEY variable is DATE, which currently has the value 19860228. The H.FIND() statement searches the hash for a key value of 19860228. If it finds it, it populates the variables MKTRF, SMB, HML, UMD, RF with the data corresponding to that KEY in the hash, and returns a value of zero. If it does not find it, the variables MKTRF, SMB, HML, UMD, RF continue to have the missing value, and the H.FIND() returns a nonzero value
\item If H.FIND() returns a zero value, indicating that it found that date in the dataset FF, the observation is output to the output dataset. Otherwise the observation is not output.   If you wanted to keep both, observations for which a corresponding observation in FF exists, and those for which a corresponding observation does not exist, you could say:

\begin{verbatim}
data msf;
    if _n_=1 then do;
        declare hash h(dataset:'ff');
        h.defineKey('date');
        h.defineData('mktrf', 'smb', 'hml', 'umd', 'rf');
	call missing(mktrf, smb, hml, umd, rf);
        h.defineDone();
	end;
    set msf;
    if h.find() = 0 then foundinFF=1;
    else foundinFF=0;
run;    

\end{verbatim}
The foundinFF variable will tell you whether the observation existed in the FF dataset or not.
\end{enumerate}

Some caveats:

\begin{itemize}
\item The hash is only useful when you are subsetting a larger dataset. If you wanted to keep observations in FF that did not exist in MSF, this form of the hash would not help.
\item The dataset that is read into the hash has to be below a certain size, otherwise it will not fit into your memory. In my situation, MSF just about fits into memory. SAS will die with an out-of-memory error if you try to load a dataset that is larger than available memory.
\end{itemize}

The hash is of enormous utility in performing sortless merges, but it is also very useful in other situations. An example of this is many-to-many merges, explored in Section~\ref{subsubsec:hasheswithmultidata}.

Because it is a headache to type out all this when you want to do merges with hashes, I wrote a macro that does it for me. See section~\ref{subsubsec:hashmergemacro} for the macro.

A nice introduction to the hash object can be found in \cite{secoskeybloom}.

\subsection{One-to-many with the DOW loop}
If you have to do a one-to-many merge on sorted datasets, you can do it with the DOW loop, which I will define as a DO-loop which contains a SET statement (see section~\ref{sec:thedowloop}). The utility of this is doubtful: since your datasets are already sorted, you might as well use the data step merge, but it's a nice illustration of what the DOW loop can do, and serves to sharpen your understanding of the DATA step.

Suppose I want to merge crsp.msf(keeping price and shares outstanding), with the CRSP mutual fund holdings dataset, which has crsp\_portno, permno, nbr\_shares, and eff\_dt. eff\_dt is the effective date of the holding.
   
   \begin{verbatim}
     data msf(rename=date=eff_dt);
        set crsp.msf(keep=permno date ret prc shrout);
        date=intnx('month', date, 1)-1;
     run;
     
     data holdings;
        set crsp.holdings(keep=crsp_portno eff_dt nbr_shares permno);
        where permno is not missing;
     run;

     proc sort data=msf;
        by permno eff_dt;

     proc sort data=holdings;
        by permno eff_dt;

     data complete;
         set msf;
	 do until(last.eff_dt);
             set holdings;
	     by permno eff_dt;
	     output complete;
	 end;
     run;

     \end{verbatim}

This reads an observation from MSF. It then enters the DO-loop, and reads all observations from HOLDINGS until the LAST.EFF\_DT=1, after which it goes to to the top of the DATA step and reads the next observation from MSF.

The obvious question is: how do you know that the permno-eff\_dt combination read from HOLDINGS is the same as that read from MSF? In this setup, you don't. To make it work, before the DATA step with the DOW loop, you need to do the following:
   
   \begin{verbatim}
     proc sort data=holdings(keep=permno eff_dt) out=permnoeffdts_holdings nodupkey;
        by permno eff_dt;

     proc sort data=msf(keep=permno eff_dt) out=permnoeffdts_msf nodupkey;
        by permno eff_dt;


    data msf;
        merge msf(in=a) permnoeffdts_holdings(in=b);
	by permno eff_dt;
	if a and b;
    run;
    
    
    data holdings;
        merge holdings(in=a) permnoeffdts_msf(in=b);
	by permno eff_dt;
	if a and b;
    run;

  \end{verbatim}

This restricts the two datasets to contain only those permno-eff\_dt combinations which exist in both.

When this is done, you run the DOW-loop as above:
   \begin{verbatim}
     proc sort data=msf;
        by permno eff_dt;

     proc sort data=holdings;
        by permno eff_dt;

     data complete;
         set msf;
	 do until(last.eff_dt);
             set holdings;
	     by permno eff_dt;
	     output complete;
	 end;
     run;
     \end{verbatim}

What happens now? Now you know that for every observation read from MSF, there is at least one observation in HOLDINGS with the same permno-eff\_dt combination. Let me now go through the logic again.

The first observation is read from MSF. The permno-eff\_dt combination of that observation exists in HOLDINGS, and because HOLDINGS contains only those combinations that are also in MSF, that combination must be the first combination encountered in HOLDINGS. We keep reading in observations from holdings (and outputting to COMPLETE) until LAST.EFF\_DT=1, at which point there are no more observations in HOLDINGS with that particular combination of PERMNO and EFF\_DT. Then we exit the DO-loop and read the next observation from MSF, and repeat the process.

\subsection{Many to many}
   \subsubsection{Using proc sql}
   Not recommended.
   \subsubsection{Using hashes with multidata}
   \label{subsubsec:hasheswithmultidata}
\subsection{Post-merge processing: merge and then calculate summary statistics}
   \label{subsec:pmp}
   eg: merge with proc sql and then sum 
   The DOW loop
   
   \section{Statistics}
   \subsection{Weighted means with negative weights}
   I often want to calculate the return to portfolios of stocks where the weights of some stocks are negative. For instance, suppose we have a dataset PORTWEIGHTS that contains 

\begin{verbatim}
permno, portweight
10001, 0.2
10006, 0.3
10010, -0.4
\end{verbatim}

We want to calculate the returns to this portfolio over January 2001.

So we say:

\begin{verbatim}
data msf;
   set crsp.msf(keep=permno date ret);
   where intnx('month', date, 1)-1 = '31Jan2001'd); *Moves date to end-of-month;
run;

\end{verbatim}

and then merge this with PORTWEIGHTS by PERMNO to get a dataset PORTWEIGHTSRETS, and then just say


\begin{verbatim}
proc means data=portweightsrets;
var ret/weight=portweight;
run;
\end{verbatim}

The problem is that SAS sets negative weights to zero when it calculates means. So permno 10010, for instance, will not be included in the portfolio.

The straightforward way to handle this is to say:



\begin{verbatim}
data portweights;
  set portweights;
  modifiedret=ret;
  if portweight<0 then modifiedret=-ret;
  absportweight=abs(portweight);
run;

proc means data=portweightsrets;
  var modifiedret/weight=absportweight;
run;
\end{verbatim}


   \subsection{Robust standard errors}
   Clustered standard errors
   White standard errors
   Newey-West standard errors
   \subsection{Fixed effects}
   \subsubsection{Brute-force}
   \label{subsubsec:febr}
   The brute-force way -which I do not recommend - to run fixed-effects models is to actually create the dummies and run proc reg including the dummies as independent variables. 
   
   Suppose I have monthly data on stock returns and I want to naively test the hypothesis that smaller stocks have higher returns. From crsp.msf I can construct a dataset with return and lagged size, following the prescription in Section \ref{subsec:avoidlag} to construct lagged sizes:
   \begin{verbatim}
     data msf(keep=permno date ret) 
     msflag(keep=permno date size rename=(size=lagsize));
        set crsp.msf(keep=permno date ret prc shrout);
        date=intnx('month', date, 1)-1;
	output msf;
	size=abs(prc)*shrout;
	date=intnx('month', date+1, 1)-1;
	output msflag;
     run;

     data msf;
        merge msf msflag;
        by permno date;
     run;
     
   \end{verbatim}

   MSF now has 

\begin{verbatim}
  PERMNO DATE RET LAGSIZE 
\end{verbatim}

Without dummies, proc reg is:
\begin{verbatim}
  proc reg data=msf;
      model ret=lagsize;
  run;
\end{verbatim}

Suppose I want permno fixed effects. Let me describe the steps I need to do this brute-force.  First, I find out how many permnos there are. Then I create that many dummies, which are variables such that, for each observation, only one can be 1, the others are all zero. Then I run proc reg including these variables on the right-hand side.

So first, I must first find out how many permnos there are. To find this, I say:

\begin{verbatim}
  proc sort data=msf out=count_of_permnos(keep=permno) nodupkey;
     where ret is not missing and lagsize is not missing;
     by permno;
  run;
  
\end{verbatim}

COUNT\_OF\_PERMNOS contains a list of permnos which have data for the regression.

To find out how many there are, we can do:

\begin{verbatim}
  data count_of_permnos;
     if _n_=1 then put nobs;
     set count_of_permnos nobs=nobs;
  run;
\end{verbatim}

This might strike you as peculiar: why is the IF statement \textit{before} the SET statement? Will the NOBS value have been populated when the IF statement is read? The answer is that NOBS is populated as soon as the data step code has been compiled, \textit{before} any observations have been read. At the same time, the datasets named in SET, MERGE, UPDATE, and DATA statements are created with the variables they are expected to contain.

Now I open the log and see what value nobs took. The log contains

\begin{verbatim}
29         data count_of_permnos;
30             if _n_=1 then put nobs;
31              set count_of_permnos nobs=nobs;
32         run;

27865
NOTE: There were 27865 observations read from the data set WORK.COUNT_OF_PERMNOS.
NOTE: The data set WORK.COUNT_OF_PERMNOS has 27865 observations and 1 variables.

\end{verbatim}

which tells me what I want to know.

I then proceed to create my dummies. I will do this using arrays:

\begin{verbatim}
  data count_of_permnos;
     set count_of_permnos;
     array permnonames (27865) permnonames1-permnonames27865;
     do i = 1 to dim(permnonames);
        permnonames(i)=0;
     end;
     permnonames(_n_)=1;
  run;
\end{verbatim}

For each observation, this creates 27865 variables named permnonames1 through permnonames27865. It puts them into an array named permnonames, so that we can do something to each of them programmatically. The do-loop sets all members of the array to zero, and then the permnonames dummy corresponding to the current permno (i.e., the \_N\_ th permno: see Subsection \ref{subsubsec:thecountern} for a description of \_N\_). I do not recommend printing this dataset out. 

I then merge these dummies back into the original dataset and run my regression:

\begin{verbatim}
  data msf;
      merge count_of_permnos msf;
      by permno;
  run;
  proc reg data=msf;
      model ret=lagsize permnonames1-permnonames27865/noint;
  run;
\end{verbatim}

The noint excludes the intercept from the model. Alternatively, you could say :

\begin{verbatim}
  proc reg data=msf;
      model ret=lagsize permnonames2-permnonames27865;
  run;
\end{verbatim}

If you'd rather not manually look at your log file to see how many dummies there need to be, you can use CALL SYMPUT in the following way:

\begin{verbatim}
  data count_of_permnos;
     if _n_=1 then call symput(``nobs'', nobs);
     set count_of_permnos nobs=nobs;
  run;
  data count_of_permnos;
     set count_of_permnos;
     array permnonames (&nobs) permnonames1-permnonames&nobs;
     do i = 1 to dim(permnonames);
        permnonames(i)=0;
     end;
     permnonames(_n_)=1;
  run;
\end{verbatim}

All I have done is to replace the number 27865 with the macro variable \&NOBS, which I created in a previous data step. You cannot use the non-macro variable nobs in defining the array because array definition requires an integer constant as dimension, not a variable.



Running this poses several problems. First: you have 27866 independent variables! The matrix to invert is enormous. This is clearly massively inefficient. Second: when you run proc reg like this, it's going to print out the values of those 27865 dummy coefficents, which you don't care about at all. See Section \ref{subsubsec:macros_reg} for an example of how to avoid this. 

\subsubsection{An efficient way with one-way fixed effects}

The efficient way is to use the Frisch-Waugh theorem which says that when you run a regression of Y on two sets of independent variables X\_1 and X\_2, the coefficients on X\_2 are the same as if you did the following:

\begin{enumerate}
  \item Run each variable in X\_2 on X\_1, get the residuals
  \item Run Y on the residuals.
\end{enumerate}

In our application, X\_1 are the dummies, X\_2 is lagsize. 

The residual from regressing a variable on dummies is the variable demeaned by group. So regressing X\_2, i.e. lagsize, on dummies is the same as demeaning lagsize by group - in this case, by permno.

To do this you can actually calculate the means using proc means by permno, merge the means in by permno, and then subtract the means, or you can do it in one step by using proc standard:

\begin{verbatim}
proc standard data= msf out=msf mean=0;
    by permno;
    var lagsize;
run;
proc reg data=msf;
    model ret=lagsize;
run;

\end{verbatim}

Be careful to include the OUT= option in PROC STANDARD, otherwise the procedure will create a new anonymous dataset with a name like DATA1 and put the demeaned variables there.

You can check that the coefficient is the same as with the dummies.

An alternative is to use proc glm with the absorb statement, but I do not completely understand proc glm, and I am reluctant to use it. This is simple, transparent, and as easy to code. Should you have multiple independent variables (other than the dummies), this extends easily: just put the additional variables on the VAR statement in the PROC STANDARD and, as usual, on the MODEL statement in PROC REG.

\subsubsection{Two-way fixed effects}

Two-way fixed effects are easily coded up using the inefficient dummy method. Since we're trying for efficiency, a better solution is to use a combination of the Frisch-Waugh Theorem and the inefficient method. 

Explicitly, first determine which one of your dummy sets has fewer elements. 

Suppose we want to run the regression in the previous subsection --- returns on lagged size --- with dummies for date and permno. Since data is monthly and the msf file has data from 1927-2007, there will be 12*81=810+162=972 date dummies. That's much smaller than the 27865 permno dummies. 

Then we create the date dummies as shown in Section \ref{subsubsec:febr}. Explicitly:

\begin{verbatim}
  data dates;
    date='31Jan1927'd;
    do while(date<='31Dec2007'd);
       output;
       date=intnx('month', date+1, 1)-1;
    end;
  run;
  data dates;
     set dates;
     array dateids (972);
     do i = 1 to dim(dateids);
        dateids(i)=0;
     end;
     dateids(_n_)=1;
  run;
\end{verbatim}

and merge these in with the original dataset by date. There are 972 date dummies named dateid1-dateid972.

Once I have the date dummies, I treat the date dummies as additional independent variables:

\begin{verbatim}
proc standard data= msf mean=0;
    by permno;
    var lagsize dateid1-dateid972;
run;

proc reg data=msf;
    model ret=lagsize dateid1-dateid972/noint;
run;

\end{verbatim}

which produces the two-way fixed effects coefficient on lagsize.

To suppress the printing of the dummy coefficients, I can say:

\begin{verbatim}

proc reg data=msf outest=est(keep=lagsize _type_) noprint tableout;
    model ret=lagsize dateid1-dateid972/noint;
run;

proc print data=est;
run;

\end{verbatim}


\subsection{Pairwise statistics with large datasets}
Correlations

\section{Efficiency}
\label{sec:effic}
\subsection{Reading into the PDV: where, keep, and in}
When using a WHERE statement, don't use formulae as far as possible. That is, say 
\begin{verbatim}
  where date>='01Jan1996'd;
\end{verbatim}
rather than
\begin{verbatim}
  where year(date)>=1996;
\end{verbatim}
WHERE statements can be used with OUTPUT statements. (WHERE=)

\subsection{Never use proc sql}

\subsection{Don't sort more than strictly necessary}
Sorting takes by far the most time of most programs. 

Use the fact that the datasets you use are already sorted. DSF and MSF are sorted by permno date. A splendid example of this is the code on the WRDS website which merges TAQ.CT with TAQ.CQ. 

Instead of sorts to merge, use hashes - Section~\ref{sec:merges}), which do not require sorting and are faster anyway.

Instead of using proc means with a by statement, that is, instead of saying 

\begin{verbatim}
proc sort data=crsp.msf out=msf;
  by date;
run;

proc means data=msf noprint;  
  by date;
  var ret;
  output out=summarystats(drop=_type_ _freq_) mean=meanret;
run;
\end{verbatim}

say

\begin{verbatim}
proc means data=crsp.msf noprint nway;  
  class date;
  var ret;
  output out=summarystats(drop=_type_ _freq_) mean=meanret;
run;
\end{verbatim}

I replaced the ``by'' with a ``class''. I also used the NWAY option, which restricts the output to the ``outremost level''. Without it, SAS produces output for all combinations of the CLASS variables. MEANING WHAT?
\subsection{Macro loops make things possible, they don't make them fast}

Instead, use data steps. One thing that macros seem good for is running rolling regressions, or computing rolling means or standard deviations. A modification of the method used in section \ref{subsec:rollingwomacros} should be used instead.
      
\section{SAS on Unix}
\subsection{Transferring files from DOS: line endings}
Files created on Windows have different line-endings from the ones used on Unix. Transferring files between systems (even program files) leads to strange results. After you transfer a file from DOS to Unix, be sure to say 

\begin{verbatim}
dos2unix dosfilename unixfilename
\end{verbatim}
The DOS line ending will often appear as \^M in files opened in Emacs. If you see that you will know what to do.

\subsection{Locating your work directory}
To tell where your work directory is, run the following code in any SAS file:
\begin{verbatim}
proc options option=work;
run;
\end{verbatim}
This prints your work directory to the log file. On Unix on WRDS, the work directory will have a name that looks like 

\begin{verbatim}
/sastemp7/SAS_workCB0F00002C49_wrds.wharton.upenn.edu.
\end{verbatim}

\subsection{Relocating your work directory}

On WRDS, your work folder is created in one of the sastemp directories. To choose which sastemp directory it is assigned to, at the prompt, you can say:
\begin{verbatim}
sas -work /sastemp11 myprogram.sas&
\end{verbatim}
instead of 
\begin{verbatim}
sas myprogram.sas&
\end{verbatim}

This will place your work folder in /sastemp11.

You can check which sastemp directory is most full at the moment by saying 

\begin{verbatim}
du -sh /sastemp*
\end{verbatim}

at the prompt. This is good to know because when you are about to run a program which you know will create an enormous file which might put you over the 200G sastemp folder limit, it is best to start your program in the most lightly-loaded sastemp folder.

There is no reason that changing the location of your work directory should affect how fast your programs run on WRDS, but I've found that if my programs seem to get ``stuck'', that is, if 

\begin{verbatim}
ps -fu `whoami`
\end{verbatim}

constantly shows that my programs are not using up any CPU time, I find that killing them and moving the work directory to a different, less lightly-loaded sastemp folder helps.

\subsection{Relocating your log and lst files}
\label{subsec:relocloglst}
Log and lst files are created, by default, in the folder in which you are when you type the sas command. This may be different from the folder in which your SAS program is. 

If your lst or log files are likely to be large and you are at or close to your space quota, running SAS will put you over the limit, causing SAS to die with a can't-write-to-file error. To get around this you can use proc printto, which places the log and lst files where you say:

\begin{verbatim}
proc printto new log='/sastemp7/test.log' print='/sastemp7/test.lst';
run;

<further commands>
\end{verbatim}

The log and the lst output from 'further commands' will be placed in the files named.

You could do the same with command line options:

\begin{verbatim}
sas -log /sastemp7/logfile -print /sastemp7/lstfile program.sas
\end{verbatim}

The difference is that with command-line options, all output is directed as you stated; with proc printto, only commands after the proc printto statement direct output as stated.

\subsection{Memory issues}


\subsection{Why does proc import hang on Unix?}
See subsection \ref{subsec:procimphangs}

\subsection{Searching for files on Unix}

To search for all files in a directory that match a particular expression, use Unix's \textit{find} command. For instance

\begin{verbatim}

  find . -name '*sas'
  
\end{verbatim}

finds all files in the current directory (.) whose names end in \textit{sas}. 

This is even more useful than it seems at first glance. You can search for text within the files:


\begin{verbatim}

  find . -name '*sas' | xargs grep 'sastemp'

\end{verbatim}

searches all the SAS files found for the string 'sastemp'. The \textit{xargs} command takes the output of \textit{find} and gives it to \textit{grep} one line at a time. 

\subsection{Utilities}

Working with SAS on Unix is much easier if you have several small scripts to help you out. I find that the idea for these utilities is what's important: the actual coding is trivial. You can use your own favourite language or shell to code these. I used perl.

I describe three such utilities.
\subsubsection{Parsing the SAS log: logsasparse}
After a SAS program finishes running, you should read the SAS log to see that there were no errors or unanticipated events. The log may be hundreds or thousands of lines. An easier way is to write a perl script that searches the log for keywords. My perl script is called logsasparse and prints out suspicious lines in the log.

I save this perl program as a file named logsasparse in a directory in my path, and I call it by saying 
\begin{verbatim}
logsasparse mysasfile.log
\end{verbatim}
The code itself is below:
\begin{verbatim}

#!/usr/bin/perl
foreach my $ARG( @ARGV)
{
    open(FH, "<$ARG") || die "$ARG inaccessible: $!";
    while(<FH>)
    {
	print $ARG," :", $.,  " ", $_ if /(not found|multiple lengths|has more than one data set with|overwrit|uninit|warning|no observations|[^_](?<!set )error(?! detection))/i;
    }
    close FH;
}
\end{verbatim}

Particularly insidious errors this catches are when a variable is uninitialized, when a variable is overwritten in a merge, and when a data step merge is many-to-many without your realizing it. These errors are insidious because they are not reported as errors in the log, merely notes or warnings.
				   
As a matter of course, you should have the option MSGLEVEL set to I. See Section~\ref{subsec:msgleveli} for details.

One nonobvious thing about this way of doing things is that if you create datasets named ERROR, which, if your program is working correctly, will have zero observations, logsasparse will print out how many observations they contain to the log file.

\subsubsection{Looking at SAS datasets: sasprint}
An apparent advantage of Windows for SAS is that  SAS datasets (*.sas7bdat files) cannot be opened and read on Unix. This bash utility I call sasprint allows you to print the first N observations of a SAS dataset.

I save this bash script as a file named sasprint in a directory in my path, and I call it by saying 
\begin{verbatim}
sasprint /wrds/crsp/sasdata/sm/msf.sas7bdat #prints 20 observations (default)

sasprint /wrds/crsp/sasdata/sm/msf.sas7bdat 30  #prints 30 observations

sasprint /wrds/crsp/sasdata/sm/msf.sas7bdat 20 'abs(prc)>5' #prints 20 observations where PRC>5

sasprint /wrds/crsp/sasdata/sm/msf.sas7bdat 20 'abs(prc)>5' 'permno date ret' #prints permno date ret

\end{verbatim}

The code follows: 

\begin{verbatim}
#!/bin/bash

if [ ! -f $1 ]; then
    echo "File $1 does not exist"
    exit
fi


fullfilename="$1"

filepath="${fullfilename%/*}"
filename=$(basename $fullfilename)  #only the filename
filename=${filename%.*} #without the extension

numberoflines=$2
if [ -z $2 ]; then
    numberoflines=20
fi

wherestatement=""
if [ ! -z "$3" ]; then
    wherestatement="where $3;"
fi

varstatement=""
if [ ! -z "$4" ]; then
    varstatement="var $4;"
fi

command="libname xtemp '$filepath/'; proc print data=xtemp.$filename (obs=$numberoflines); $wherestatement $varstatement"
blank="                                                              " #Hack: sas seems to chop off the first few characters

echo "$blank $command" | sas -sysin /dev/stdin -print /dev/stdout -log /dev/null

#Check if SAS returned an error
if [ $? -ne 0 ]; then  
    echo "Error in program: rerunning for log"
    echo "$blank $command" | sas -sysin /dev/stdin -log /dev/stdout -print /dev/null
fi
				   
\end{verbatim}

Notice the use of the sysin option, which tells SAS where to find code (in this case, in standard input: STDIN). Notice also the redirection of the log and lst files (see section~\ref{subsec:relocloglst}).


\subsubsection{Killing all sas processes: saskill}

I often want to stop a SAS process midway. The proper way to do this is to list my processes:

\begin{verbatim}
    ps -fu `whoami`
\end{verbatim}
to find the process ID (PID) of the SAS process and kill it by typing
\begin{verbatim}
   kill -9 <PID>
\end{verbatim}

On some Unix systems this can be done in one step my using the killall command, which takes as its argument the commandname which you want to kill all instances of:

\begin{verbatim}
   killall sas
\end{verbatim}

This command is not available on all systems. However, there's nothing easier than automating the two-step process above:

\begin{verbatim}


#!/usr/bin/perl -w

my @userpids = `ps -u $ENV{'USER'} -o pid=`; #Get a list of PIDs I (that is, $ENV{'USER'}) am running

my @saspids=`ps -C sas -o pid=`; #Get a list of PIDs corresponding to SAS processes

chomp(@userpids); #Remove trailing newlines
chomp(@saspids); #Remove trailing newlines

my %original = ();
my @isect = ();

map { $original{$_} = 1 } @userpids;
@isect = grep { $original{$_} } @saspids; #Find the intersection of the two lists

foreach my $pid (@isect) #Kill each process whose PID is in the intersection
{
    print "Killing pid: $pid\n";
    my $a=`kill -9 $pid`;
}


\end{verbatim}

I save this as a file named saskill in a folder in my path, make it executable, and call it by saying 

\begin{verbatim}
  saskill
\end{verbatim}
 
at the prompt.

\subsection{The autoexec.sas file}
\label{subsec:autoexec}
The autoexec.sas file is a file in your home directory (Unix) or in C:\ (Windows). It contains SAS statements that are executed each time a SAS session is started. It is a useful place to keep library definitions, options statements, and \%include statements for macro files that you use constantly.

A section of my autoexec file follows:

\begin{verbatim}
%sysexec(perl ~/utils/cleanworksas);
    
%include '~/utils/sasmacros/macrolist.sas';
options linesize=130;
options dtreset;
options mprint symbolgen msglevel=i;

proc options option=work;
run;

%put The PID of this SAS process is: &sysjobid;
\end{verbatim}
The \%sysexec statement launches a shell to execute the command given. Here it runs perl on my cleanworksas program, which cleans out old work directories from previous SAS sessions. You're unlikely to need this on WRDS, because this is done automatically there.

macrolist.sas contains all the macros I referred to in Section \ref{subsec:frequsedasmac}. 

linesize sets the width of the page (80 is good for printing on A4 paper). options dtreset prints the current date and time anytime the date or time has to be written to the log or lst files, instead of the date and time the program started, which is the default. 

The proc options statement prints the name of the work folder to the log. This is useful in case I want to look at the datasets in WORK while the program is running, or delete them manually if the program exits abnormally.

\&sysjobid is a macro variable, automatically created by SAS, containing the Unix process ID of the current SAS job. This is a useful thing to have when you have three jobs with the same name running and you want to kill one, but aren't sure which is which.

\subsection{The .sasv9.cfg file}

The .sasv9.cfg file is a place to put options that would otherwise go on the command line when you invoke SAS. For instance, if I wanted to change my work directory to /sastemp7, I would ordinarily say 

\begin{verbatim}
  sas -work /sastemp7 mysasfile.sas &
\end{verbatim}

when I run mysasfile.sas. If I want my work directory to \textit{always} be in /sastemp7, I would open the file .sasv9.cfg and put this single line in it:

\begin{verbatim}
 -work /sastemp7
\end{verbatim}

Now every time SAS is invoked, it behaves as if it were invoked with 

\begin{verbatim}
  sas -work /sastemp7 
\end{verbatim}

				   

\subsection{SAS with X-Windows}

Horrendously slow, even with a wired connection. Windows pop up all over the place. Not recommended.

\section{Further interesting topics}
\subsection{What really happens in the merge statement}
\label{subsec:whatreally}
\subsection{The fascinating Do-loop}
\label{subsec:thefascdoloop}
\bibliography{bib}
\end{document}
