Contributor
Posts: 65

# Coding for 2 Regressions

Hello SAS support,

Step 3 of the following code is not working.  I want fqtr=4 and fpi=9.  The first 2 steps work fine, but the problem is with the 3rd step (fpi=9).  SAS gives me an error message saying ERROR: WHERE clause operator requires compatible variables.  If this gets fixed, then I would think the rest of this code would work.

data etr.d;

set etr.combined;

input permno fqtr fpi fyear rdq date9.;

cards;

10001 2 9 2012 05jul2014

10001 4 9 2010 08nov2015

10002 4 9 2009 09oct2015

10003 1 9 2012 002jan2015

10004 4 9 2013 01Dec2015

;

run;

data etr.e;

set etr.d(where=(fqtr=4));

run;

data etr.f;

set etr.e(where=(fpi=9));

run;

proc sort data=etr.f; by permno rdq;

run;

proc sort data=etr.e; by permno rdq;

run;

proc sort data=etr.crspmont; by permno;

run;

data etr.g;

merge etr.f etr.e etr.crspmont;

by permno;

run;

After the step above works, I need to calculate the 3 highlighted items from the models below.  Then, I will need to merge these 3 calculations into the etr.combined dataset by permno.

Model 1 = Analyst’s Forecast Errors = β1Dummy ETR + β2SIZE + β3COV + β4EVOL + β5RETVOL + β6ROA + β7EP + β8EPxDEP + β9BM + β10SGR + β11Change in PPE + β12RET12 + β13ACC

Analyst’s Forecast Errors = Earnings/price (E/P) ratio less the median analysts’ E/P forecast made 8 months before fiscal year-end, divided by the closing share price at the prior year-end, which is formalized below:

Analyst’s Forecast Errors = ((NI/CSHO) – MEDEST)/PRCC_F 1 year before FYEAR

If MEASURE =”EPS”

If FISCALP = “ANN”

If month(datadate) = 12 and month (statpers) ne 4 then delete;

If month(datadate) = 11 and month (statpers) ne 3 then delete;

If month(datadate) = 10 and month (statpers) ne 2 then delete;

If month(datadate) = 9 and month (statpers) ne 1 then delete;

If month(datadate) = 8 and month (statpers) ne 12 then delete;

If month(datadate) = 7 and month (statpers) ne 11 then delete;

If month(datadate) = 6 and month (statpers) ne 10 then delete;

If month(datadate) = 5 and month (statpers) ne 9 then delete;

If month(datadate) = 4 and month (statpers) ne 8 then delete;

If month(datadate) = 3 and month (statpers) ne 7 then delete;

If month(datadate) = 2 and month (statpers) ne 6 then delete;

If month(datadate) = 1 and month (statpers) ne 5 then delete;

Dummy ETR = Conditions that need to be met (FISCALP=”ANN”):

1. Actual EPS (AEPS ≥ Forecasted EPS (FEPS) at 4th quarter
2. If ETR (3rd quarter) is used (rather than ETR (4th quarter)), then AEPS < FEPS
3. (Pretax Income at 3rd Quarter * (1 – ETR (3rd Quarter)) * “Adjustment/Split Factor”)/CSHO when FQTR=3 is short of 3rd Quarter Analyst EBS

Dummy ETR =    1 if A, B, and C are met

0 otherwise (more formalized statements as follows):

If ((PI * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) < MEDEST then INT1 = 1 (MEASURE=”EPS”)

If ETR (4th Quarter) < ETR (3rd Quarter) then INT1 = 1

If ((PI * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) ≥ MEDEST then INT1 = 0 (MEASURE=”EPS”)

If ETR (4th Quarter) ≥ ETR (3rd Quarter) then INT1 = 0

If (MEDEST/(1 – ETR (3rd Quarter)) – ((PIY * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) is between \$0.01 and \$0.05 when FQTR=3 then INT1 = 1 (MEASURE=”EPS”)

If (MEDEST/(1 – ETR (3rd Quarter)) – ((PIY * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) is NOT between \$0.01 and \$0.05 when FQTR=3 then INT1 = 0 (MEASURE=”EPS”)

INT1 = 1 (meaning that the conditions above when INT1=1 are met)

If ETR (3rd Quarter) then AEPS < FEPS

FISCALP=”ANN”

Legend:

AEPS = NI/CSHO

FEPS = MEDEST

If MEASURE=”EPS” and

ETR (3rd Quarter) = TXTY/(NIY + TXTY)

If FQTR = 3

ETR (4th Quarter) = TXTY/(NIY + TXTY)

If FQTR = 4

SIZE = Log of MKVALT 1 year before FYEAR

COV = Log of NUMEST

If FPI = 9

EVOL = Standard deviation of (NI/(Average of AT 1 year before FYEAR and AT 2 years before FYEAR) for 5 years in a row

For example, if we are in 2010, we need standard deviation of:

2009 NI/(average of 2007 and 2008 AT)

2008 NI/(average of 2006 and 2007 AT)

2007 NI/(average of 2005 and 2006 AT)

2006 NI/(average of 2004 and 2005 AT)

2005 NI/(average of 2003 and 2004 AT)

RETVOL = Standard deviation of RET (from etr.g) during the 12-month period before RDQ

If FPI=9 and FQTR=4 (which should already be sorted)

ROA = NI/(Average of AT 1 year before FYEAR and AT 2 years before FYEAR)

EP = (NI/PRCC_F) 1 year before FYEAR

EP x DEP = (NI/PRCC_F 1 year before FYEAR) times a dummy variable with one if EP is negative and 0 otherwise

BM = (AT – LT)/MKVALT

SGR = (SALE 1 year before FYEAR – SALE 2 years before FYEAR)/SALE 2 years before FYEAR

Change in PPE = (PPENT – PPENT 1 year before FYEAR)/

(Average of AT Prior Year and AT 2 years ago)

RET12 = RET (from etr.g) during the 12-month period 9 months before FYEAR

If FPI=9

ACC = (NI 1 year before FYEAR – OANCF 1 year before FYEAR)/AT 1 year before FYEAR

Model 2: CAR = β1Dummy ETR + β2SIZE + β3RETVOL + β4BTM

CAR = RET – SPRTRN (from etr.c file) cumulatively over 3 days as follows:

(1 day before RDQ + RDQ  + 1 day after RDQ)

If FQTR = 4 (which it already should be)

Dummy ETR = Conditions that need to be met (FISCALP=”ANN”):

1. Actual EPS (AEPS ≥ Forecasted EPS (FEPS) at 4th quarter
2. If ETR (3rd quarter) is used (rather than ETR (4th quarter)), then AEPS < FEPS
3. (Pretax Income at 3rd Quarter * (1 – ETR (3rd Quarter)) * “Adjustment/Split Factor”)/CSHO when FQTR=3 is short of 3rd Quarter Analyst EBS

Dummy ETR =    1 if A, B, and C are met

0 otherwise (more formalized statements as follows):

If ((PI * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) < MEDEST then INT1 = 1 (MEASURE=”EPS”)

If ETR (4th Quarter) < ETR (3rd Quarter) then INT1 = 1

If ((PI * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) ≥ MEDEST then INT1 = 0 (MEASURE=”EPS”)

If ETR (4th Quarter) ≥ ETR (3rd Quarter) then INT1 = 0

If (MEDEST/(1 – ETR (3rd Quarter)) – ((PIY * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) is between \$0.01 and \$0.05 when FQTR=3 then INT1 = 1 (MEASURE=”EPS”)

If (MEDEST/(1 – ETR (3rd Quarter)) – ((PIY * (1 – ETR (3rd Quarter)) * ADJSPF)/CSHO) is NOT between \$0.01 and \$0.05 then INT1 = 0 (MEASURE=”EPS”)

INT1 = 1

If ETR (3rd Quarter) then AEPS < FEPS

Legend:

AEPS = NI/CSHO

FEPS = MEDEST

If MEASURE=”EPS” and

ETR (3rd Quarter) = TXTY/(NIY + TXTY)

If FQTR = 3

ETR (4th Quarter) = TXTY/(NIY + TXTY)

If FQTR = 4

SIZE = Log of MKVALTQ

If FQTR = 3

RETVOL = Standard deviation of 100 trading-day return (RET from etr.g) prior to 2 days before RDQ

If FPI = 9 and FQTR = 4 (which should already be sorted)

BTM = (ATQ – LTQ)/MKVALTQ

If FQTR = 3

After using the following code (which I used and it worked), I need to calculate cumulative abnormal return (CAR) as described above.  Then, I will need to merge this calculation into the etr.combined dataset by permo.

data etr.a;

set etr.combined;

input permno fqtr rdq date9.;

cards;

10001 2 05jul2014

10001 4 08nov2015

10002 4 09oct2015

10003 1 002jan2015

10004 4 01Dec2015

;

run;

data etr.b;

set etr.a(where=(fqtr=4));

orig=rdq;

rdq=orig-1; output;

rdq=orig+1; output;

rdq=orig; output;

drop orig;

run;

proc sort data=etr.b; by permno rdq;

run;

proc sort data=etr.a; by permno rdq;

run;

proc sort data=etr.crdarevo; by permno;

run;

data etr.c;

merge etr.b etr.a etr.crdarevo;

by permno;

run;

2 things to point out about the 2 steps above:

1. In the separate SAS files I am using that you will see in the codes (etr.crspmont and etr.crdarevo), RET is the same name for monthly stock returns (in etr.crspmont) and daily stock returns (in etr.crdarevo).  1 of these will have to be renamed before merging into the etr.combined dataset because they are obviously not the same thing.

2. Maybe 2 separate merges is better (meaning creating a copy of etr.combined) because some observations will apply to the 1st calculation (analyst's forecast errors) and some observations will apply to the 2nd calculation (CAR).  These 2 calculations do not need to have the same number of observations.

Please help me with coding for the 2 regressions in the large section above.  As you see, detail is provided for the calculations that I need.  I need the SAS coding for them.  The following is 1 line from the etr.combined dataset to make it easier for you to comprehend what I am trying to do.

 GVKEY DATADATE FYEAR cusip9 CONSOL INDFMT DATAFMT POPSRC CURCD COSTAT CONM TIC CIK FYR AT LT PPENT NI PI REVT SALE OANCF CSHO MKVALT PRCC_C PRCC_F FYEARQ FQTR DATAFQTR DATACQTR CURCDQ RDQ ATQ LTQ NIQ TXTQ CSHPRY EPSPIY EPSPXY NIY PIY TXTY MKVALTQ crsp_cusip PERMNO DATE cusip6 TICKER MEASURE STATPERS FPI OFTIC cusip8 CNAME FISCALP NUMEST MEDEST MEANEST ACTUAL ANNDATS_ACT ADJSPF USFIRM 021542 20031231 2003 000360206 C INDL STD D USD A AAONINC AAON 0000824142 12 102.0850 34.6570 37.4500 14.2270 21.8530 148.8450 148.8450 16.4690 12.5200 243.0132 19.4100 19.4100 2003 4 2003Q4 2003Q4 USD 20040211 102.0850 34.6570 3.7400 1.2600 12.6850 1.1200 1.1200 14.2270 21.8530 7.6260 0.0000 00036020 76868 20141231 000360 AAON EPS 20040115 1 AAON 00036020 AAON ANN 2 0.21 0.21 0.2114 20040211 3.374998 1

Etr.c has the same categories (although most are blank entries in etr.c) as etr.combined.  Etr.c also has RET and SPRTRN, which I need for the CAR calculation.

The most important variables in the 2 regressions are the dependent variables (of course) and the Dummy ETR independent variable in each regression (which is the same in both regressions).  For some reason, if you cannot figure out code for some of the other variables in either regression, it's fine.  Please start with the code for the 2 dependent variables and the Dummy ETR independent variable.

I will call on Monday to discuss.  I'm sure this will take a good amount of time, by your help is greatly appreciated.  I am certainly inexperienced in SAS.  I hope I can do some training this summer.  In the meantime, let's try to get these 2 regressions done first.

Thanks so much!

SAS Employee
Posts: 340

## Re: Coding for 2 Regressions

In your first step you are using a set statement and an input statement (that reads from the cards section).

With this code you are basically merging row-by-row etr.combined and the 5 rows that you have in cards.

In etr,combined the variable fpi is already defined as character.

Probably you want to rename this variable in the etr.combined dataset or use some other name in the input statement.

Discussion stats