BookmarkSubscribeRSS Feed
jjadall1
Quartz | Level 8

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. 

GVKEYDATADATEFYEARcusip9CONSOLINDFMTDATAFMTPOPSRCCURCDCOSTATCONMTICCIKFYRATLTPPENTNIPIREVTSALEOANCFCSHOMKVALTPRCC_CPRCC_FFYEARQFQTRDATAFQTRDATACQTRCURCDQRDQATQLTQNIQTXTQCSHPRYEPSPIYEPSPXYNIYPIYTXTYMKVALTQcrsp_cusipPERMNODATEcusip6TICKERMEASURESTATPERSFPIOFTICcusip8CNAMEFISCALPNUMESTMEDESTMEANESTACTUALANNDATS_ACTADJSPFUSFIRM
021542200312312003000360206CINDLSTDDUSDAAAONINCAAON000082414212102.085034.657037.450014.227021.8530148.8450148.845016.469012.5200243.013219.410019.4100200342003Q42003Q4USD20040211102.085034.65703.74001.260012.68501.12001.120014.227021.85307.62600.0000000360207686820141231000360AAONEPS200401151AAON00036020AAONANN20.210.210.2114200402113.3749981

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!

Jadallah

1 REPLY 1
gergely_batho
SAS Employee

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 858 views
  • 0 likes
  • 2 in conversation