The macro I use is borrowed from Adrian’s work. Thanks Adrian.

A related post can be found here: http://kaichen.work/?p=1365.

libname local "path_to_folder"; options mprint; %macro lowcase(dsn); %let dsid=%sysfunc(open(&dsn)); %let num=%sysfunc(attrn(&dsid,nvars)); %put # data &dsn; set &dsn(rename=( %do i = 1 %to # /*function of varname returns the name of a SAS data set variable*/ %let var&i=%sysfunc(varname(&dsid,&i)); &&var&i=%sysfunc(lowcase(&&var&i)) /*rename all variables*/ %end;)); %let close=%sysfunc(close(&dsid)); run; %mend lowcase; data temp; set local.filename; run; %lowcase(temp) proc export data= temp outfile= "path_to_folder/filename" dbms= dta replace; run;

]]>

Both WRDS and Dick-Nielsen’s codes remove cancellations, corrections, reversals, and double counting of agency trades. Dick-Nielsen’s code provides a few more options, e.g., remove commissioned trades.

]]>I find two useful articles from Stata’s official website:

Can you explain Chow tests?

How can I compute the Chow test statistic?

Suppose we do following regressions separately in two groups:

`regress y x1 x2 if group==1`

and `regress y x1 x2 if group==2`

Then following commands will test the equality of coefficients on `x1`

and `x2`

:

`ge g2=(group==2)`

`regress y c.x1##i.g2 c.x2##i.g2`

`contrast g2 g2#c.x1 g2#c.x2, overall`

Stata’s official website gives an example of the output:

In this example, to test the equality of coefficients on `x1`

and `x2`

, 6.06 and 2.80 are the F-stats that we are looking for.

First, it is only meaningful to count the number at a specified date.

Second, how to define “an analyst is actually following a firm”? I use the following definition: if an analyst issued any forecast (EPS or stock price or sales, anything) within a certain window (e.g., 180 days) before the specified date, then the analyst will be counted in. This definition ensures that the analyst is “actively” following the firm.

That is why my macro requires two arguments: DATE and WINDOW. This macro is used to answer such question—at a specified date, how many analysts are actively following Firm A, B, …?

%MACRO ANALYST_COUNT(INFILE=, TICKER=, DATE=, WINDOW=, OUTFILE=); /* This macro is used to count the number of analysts who followed a */ /* specific firm at a specified date (DATE). Any analyst who issued any */ /* forecast during the window (WINDOW) before the specified date (DATE) */ /* will be counted in. */ /* This macro use both Detailed History Unadjusted (EPS for US Region) */ /* and Unadjusted (Non-EPS for US Region). INFILE should contain IBES */ /* Ticker (TICKER) and DATE. */ options mprint; /* Stack Detailed History Unadjusted (EPS for US Region) and */ /* Unadjusted (Non-EPS for US Region). */ data detu; set ibes.detu_epsus ibes.detu_xepsus; run; /* Merge analysts who issued a forecast during the window. */ proc sql; create table ibes1 as select a.*, b.estimator, b.analys, b.anndats from (select distinct &TICKER, &DATE from &INFILE) a, detu b where not missing(a.&TICKER) and a.&TICKER=b.ticker and not missing(a.&DATE) and a.&DATE-&WINDOW+1<=b.anndats<=a.&DATE and not missing(b.value); quit; /* Retain the most recent forecast from a specific analyst. */ proc sort data=ibes1; by &TICKER &DATE estimator analys descending anndats; run; proc sort data=ibes1 out=ibes2 nodupkey; by &TICKER &DATE estimator analys; run; /* Count the number of analysts who issued a forecast during the window. */ proc sql; create table ibes3 as select distinct &TICKER, &DATE, count(anndats) as analyst_count from ibes2 group by &TICKER, &DATE; quit; /* Merge INFILE with number of analysts */ proc sql; create table &OUTFILE as select a.*, b.analyst_count from &INFILE a left join ibes3 b on a.&TICKER=b.&TICKER and a.&DATE=b.&DATE; quit; proc sql; drop table detu, ibes1, ibes2; quit; %MEND;

]]>

Writing regular expression is work of art! You can find building blocks of regular expression here. I create this post to gather examples of regular expression that will solve certain text search questions. I will grow this post continuously.

]]>**Change variable names to all lowercase**

We need to use the command `rename`

. Instead of renaming variables one at a time, we can rename all variables in a single command (thanks Steve):

rename _all, lower

A related post can be found here: http://kaichen.work/?p=1483.

**Change values of string variables to all lowercase**

`ustrlower(string_variable)`

or `strlower(string_variable)`

will do the trick. Instead of applying `ustrlower`

or `strlower`

function to string variables one by one, we can benefit from lowercasing values of all string variables in a short loop. The following loop will first check the type of a variable. If it is a string variable, then change the value of the variable to all lowercase.

foreach var of varlist _all { local vartype: type `var' if substr("`vartype'",1,3)=="str" { replace `var'=ustrlower(`var') } }

]]>

`egen compdatadate=eom(fiscalmonth fiscalyear)`

`format compdatadate %td`

To be continued …

]]>`tabulate varname`

command is handy in Stata, but sometimes it returns a too long result, if `varname`

contains too many unique values.
The third-party command, `groups`

, will solve the problem by showing top values only. Please use `ssc install groups`

to install `groups`

. The usage of `group`

is very similar to `tabulate`

. Here are some examples:

. sysuse auto (1978 Automobile Data) . groups mpg, order(h) select(5) +-------------------------------+ | mpg Freq. Percent Cum. | |-------------------------------| | 18 9 12.16 12.16 | | 19 8 10.81 22.97 | | 14 6 8.11 31.08 | | 21 5 6.76 37.84 | | 22 5 6.76 44.59 | +-------------------------------+ . groups mpg, order(h) select(f >= 3) +-------------------------------+ | mpg Freq. Percent Cum. | |-------------------------------| | 18 9 12.16 12.16 | | 19 8 10.81 22.97 | | 14 6 8.11 31.08 | | 21 5 6.76 37.84 | | 22 5 6.76 44.59 | |-------------------------------| | 25 5 6.76 51.35 | | 16 4 5.41 56.76 | | 17 4 5.41 62.16 | | 24 4 5.41 67.57 | | 20 3 4.05 71.62 | |-------------------------------| | 23 3 4.05 75.68 | | 26 3 4.05 79.73 | | 28 3 4.05 83.78 | +-------------------------------+

]]>

`and`

and `or`

in `if`

statement, compared to SAS. For example:
In SAS, we can write `if 2001 <= fyear <= 2010`

. But in Stata, we usually write: `if fyear >= 2001 & fyear <= 2010`

.

In fact, Stata provides a handy `inrange`

function. The above `if`

statement can be written as: `if inrange(fyear, 2001, 2010)`

.

Similarly, Stata provides another `inlist`

function. The syntax is `inlist(z, a, b, ...)`

, which returns 1 if z = a or z = b … In `if`

statement, it is equivalent to `if z = a | z = b | ...`

First of all, please refer to this post to see Stata commands to test equality of mean and median.

However, it is time-consuming to glean numbers from the output of these Stata commands and place them in a table. It is even more struggling that you have to repeat the tedious process every time you update your sample.

I write Stata codes to streamline the process. The codes vary between unpaired (i.e., unmatched) data and paired data.

**Unpaired data**

The above example is unpaired data, i.e., suspect firm-years and other firm-years are not 1-to-1 or 1-to-m matched. One usage of unpaired data is the first step of Heckman’s two-step procedure, in which two groups of observations (i.e., the group that will be selected into the second step and the group that will not be selected into the second step) **are stacked vertically** in the dataset. The following codes are used for unpaired data. You only need to modify the first two lines to suit your data. The codes will generate a table in Stata’s output window like this:

You can then select the output and right-click “Copy as table” and paste in Excel for a quick edit. The codes use t-test for mean and Wilcoxon rank-sum test for median.

local vars retsd cfosd lnilliq authpct //put your variables here local group grpid //"grpid" is your group indicator that takes 1 and 0 foreach v in `vars' { di "`v'" ttest `v', by(`group') local mean_`v'_mean_0=round(r(mu_1),.001) local mean_`v'_mean_1=round(r(mu_2),.001) local mean_`v'_diff=`mean_`v'_mean_1'-`mean_`v'_mean_0' local mean_`v'_p=r(p) } foreach v in `vars' { sum `v' if `group'==0, detail local p50_`v'_p50_0=round(r(p50),.001) sum `v' if `group'==1, detail local p50_`v'_p50_1=round(r(p50),.001) ranksum `v', by(`group') local p50_`v'_n_0=r(N_1) local p50_`v'_n_1=r(N_2) local p50_`v'_diff=`p50_`v'_p50_1'-`p50_`v'_p50_0' local p50_`v'_p=2*normprob(-abs(r(z))) } qui { noi di _newline noi di "{hline 115}" noi di _col(15) "{c |} `group' = 1" /// _col(45) "{c |} `group' = 0" /// _col(75) "{c |} Diff" noi di _col(16) "{hline 100}" noi di _col(15) "{c |} Mean" /// _col(25) "{c |} Median" /// _col(35) "{c |} N" /// _col(45) "{c |} Mean" /// _col(55) "{c |} Median" /// _col(65) "{c |} N" /// _col(75) "{c |} Mean" /// _col(85) "{c |} P" /// _col(95) "{c |} Median" /// _col(105) "{c |} P" noi di "{hline 115}" foreach v in `vars' { noi di %12s abbrev("`v'",12) /// _col(15) "{c |}" %8.3f `mean_`v'_mean_1' /// _col(25) "{c |}" %8.3f `p50_`v'_p50_1' /// _col(35) "{c |}" %8.0f `p50_`v'_n_1' /// _col(45) "{c |}" %8.3f `mean_`v'_mean_0' /// _col(55) "{c |}" %8.3f `p50_`v'_p50_0' /// _col(65) "{c |}" %8.0f `p50_`v'_n_0' /// _col(75) "{c |}" %8.3f `mean_`v'_diff' /// _col(85) "{c |}" %8.3f `mean_`v'_p' /// _col(95) "{c |}" %8.3f `p50_`v'_diff' /// _col(105) "{c |}" %8.3f `p50_`v'_p' } noi di "{hline 115}" }

**Paired data**

A typical usage of paired data is to identify a matched control group for the treatment group. For example, identify a matched firm-year for an event firm-year based on a set of characteristics (same industry, same year, similar size and book-to-market), or identify a matched firm for every event firm based on the closest propensity score (i.e., propensity score matching).

The following table is an example that compares the mean and median of two matched groups—restating firms and non-restating group. Each restating firm is matched with a non-restating firm.

Because of this matching relationship, every event firm and its control firm will be placed in the same row in the dataset. In other words, event firms and control firms are **aligned horizontally**. The following codes are used for paired data. You only need to modify the first two lines to suit your data. You must specify the same number of variables in the matched order in the first two lines. In other words, the first variable in the first line must be paired with the first variable in the second line, and so on. The codes will generate a table in Stata’s output window like this:

The codes use paired t-test for mean and Wilcoxon rank-sign test for median.

//put your paired variables in the first two lines. 1-to-1 correspondece is must local agrp "drpre4 drpre3 drpre2 drpre1" //e.g., treatment group local bgrp "mdrpre4 mdrpre3 mdrpre2 mdrpre1" //e.g., control group local n : word count `agrp' forvalues i = 1/`n' { local a : word `i' of `agrp' local b : word `i' of `bgrp' ttest `a'=`b' local mean_`a'=round(r(mu_1),.001) local mean_`b'=round(r(mu_2),.001) local mean_`a'_diff=`mean_`a''-`mean_`b'' local n_`a'=r(N_1) local mean_p_`a'=r(p) sum `a', detail local p50_`a'=round(r(p50),.001) sum `b', detail local p50_`b'=round(r(p50),.001) signrank `a'=`b' local p50_`a'_diff=round(`p50_`a''-`p50_`b'',.001) local p50_p_`a'=2*normprob(-abs(r(z))) } qui { noi di _newline noi di "{hline 120}" noi di _col(30) "{c |}" /// _col(40) "{c |} Var1" /// _col(60) "{c |} Var2" /// _col(80) "{c |} Diff" noi di _col(41) "{hline 80}" noi di %27s "Paired Var1 and Var2" /// _col(30) "{c |} N" /// _col(40) "{c |} Mean" /// _col(50) "{c |} Median" /// _col(60) "{c |} Mean" /// _col(70) "{c |} Median" /// _col(80) "{c |} Mean" /// _col(90) "{c |} P" /// _col(100) "{c |} Median" /// _col(110) "{c |} P" noi di "{hline 120} forvalues i = 1/`n' { local a : word `i' of `agrp' local b : word `i' of `bgrp' noi di %27s abbrev("`a' vs `b'",27) /// _col(30) "{c |}" %8.0f `n_`a'' /// _col(40) "{c |}" %8.3f `mean_`a'' /// _col(50) "{c |}" %8.3f `p50_`a'' /// _col(60) "{c |}" %8.3f `mean_`b'' /// _col(70) "{c |}" %8.3f `p50_`b'' /// _col(80) "{c |}" %8.3f `mean_`a'_diff' /// _col(90) "{c |}" %8.3f `mean_p_`a'' /// _col(100) "{c |}" %8.3f `p50_`a'_diff' /// _col(110) "{c |}" %8.3f `p50_p_`a'' } noi di "{hline 120} }

]]>