Lag Functions

[krg1140]

7 Using the lag function, create an indicator variable, PY_RESTATEMENT, coded 1 if the company issued a restatement in the prior year, and coded 0 otherwise.

data sql.Firm_data; set sql.Firm_data;
py_restatement=lag(restatement);
if restatement ne lag(restatement) then py_restatement=1;
else py_restatement=0;
run;

however this isn't working, any ideas?

[Tom]

What does "issued a restatement in the prior year" mean?

What does the RESTATEMENT variable contain?
How many firms are in the dataset?
Is the dataset order by firm and year?
Are there any missing years?

Please share some sample data with expected results.

Hint: Do not overwrite your input dataset, that will make debugging impossible because you no longer have the original input.

Try one of these.

If RESTATEMENT is already a 1/0 (true false) flag variable.

data want;
  set have;
  py_restatement=lag(restatement);
run;

If RESTATEMENT having a non-zero or  non-missing value means there was a restatement.

data want;
  set have;
  py_restatement= not not lag(restatement);
run;

[krg1140]

So here are the prior steps that led to this:

1. Create a new indicator variable to identify years where a restatement was issued.  If FILE_DATE is greater than 0, RESTATEMENT should equal 1 and otherwise should be 0.
2. Using PROC SQL, perform an inner join to merge Audit Fees 2 and Firm Data.  Merge observatiosn where COMPANY_FKEY equals CIK and observations have the same fiscal year (FYEAR).
3. Using the lag function, create an indicator variable, PY_RESTATEMENT, coded 1 if the company issued a restatement in the prior year, and coded 0 otherwise.
4. Create a variable, NEW_AUDITOR, coded 1 if the auditor in the current year is not the same as the auditor in the prior year, and 0 otherwise.
   • Hint: First use the lag function to create prior GVKEY and prior AUDITOR_FKEY variables.  Then delete observations where prior GVKEY and GVKEY are not the same (i.e. the firm changed from one row to the next).  Finally, for the remaining observations, create an indicator variable coded 1 if the auditor identifier and prior year identifier (AUDITOR_FKEY) are not the same.
5. The mean value of NEW_AUDITOR is the percentage of observations where there was a new auditor.  Obtain the mean value of NEW_AUDITOR for observations where PY_RESTATEMENT is equal to one and observations where PY_RESTATEMENT is equal to 0.  Are firms more likely to get a new auditor if they issued a restatement in the prior year?
   • Hint: You may have to research how to use the proc means command for groups.

Basically I want to lag when restatement = 1 or 0. However I, unfortunately, have to use the lag statement to get full credit. I don't know how to lag, really, so the last 3 statements are impossible for me. 

[Tom]

I don't think the LAG() part is really that hard for this.

Your step 3 is really just the code I showed before.  If you have already created RESTATEMENT as 0/1 flag variable then the simple LAG(RESTATEMENT) will get you the value of RESTATEMENT from the previous observation.  The key thing is that the data needs to be ordered properly, otherwise the previous value of restatement is not the previous year's value. 

But you need to take into account that you have multiple company.  You don't want the flag on the first observation for a company to have the value from the last observation for the previous company.

Let's use COMPANY and YEAR as the variables that define the company and the year.

data want;
  set have;
  by company year ;
  py_restatement=lag(restatement);
  if first.company then py_restatement=0;
run;