3 Versions of Dependent Variable in Same Dataset

[jjadall1]

Hello SAS Support Communities,

Please see the code below.  For your information, cik in the code is a unique identifier for each company (e.g., Lowe's, Home Depot, etc.)  Let me give you an example of what I'm looking for.  Let's say a company has a 12/31 fiscal year-end.  There's a restatement that covers January 1, 2009 (rpb in the code below) through June 30, 2012 (rpe in the code below) that is disclosed on June 15, 2014.

I would like 3 versions of a categorical dependent variable in the same dataset as follows:

1. One with a categorical dummy variable = 1 if restatement_key (identifier of each restatement (January 1, 2009 through June 30, 2012 in my example)) is in year 1 of the restatement.  That would be if first.restatement key then do, correct?  In my example, the only categorical dependent variable with a value of 1 would be fyear = 2009.

2. One with a categorical dummy variable = 1 if fyear is between y1 and y2.  In my example, the categorical dependent variable would have a value of 1 when fyear = 2009, 2010, 2011, and 2012.

3. One with a categorical dummy variable = 1 only in the year of disclosure.  In my example, the categorical dependent variable would have a value of 1 when fyear = 2014.

data SAS_data.restatement1;

set SAS_data.restatement;

rpb = restated_period_begin;

rpe = restated_period_ended;

RstateDays = (Datdif (rpb, rpe, 'act/act'))+1;

y1 = year(restated_period_begin);

y2 = year(restated_period_ended);

y3 = year(disclosure_date);

run;

data SAS_data.restatement1a;

set SAS_data.restatement1;

If RstateDays LT 350 then delete;

run;

data SAS_data.restatement2;

set SAS_data.restatement1a;

drop rpb;

drop rpe;

run;

 proc sort data=SAS_data.restatement2 out=SAS_data.restatement2a;

 by cik;

 run;

 /*create observation for each year restated [include begin year and end year] */

 data SAS_data.restatement3;

 set SAS_data.restatement2a;

 by cik;

 if cik then do;

 do fyear=y1 to y3;

 output;

 end;

 end;

 run;

Please let me know how I can get the three dependent variables in the same dataset starting with the code I already have. 

God bless, best regards, and thank you so much for your help,
Jadallah

[error_prone]

Please post test-data and the expected result-dataset, making it a lot easier to understand your problem.

[jjadall1]

Hello,

The original dataset (restatement) is attached.  After running some code, another dataset is attached (restatement4).  The three dependent variables should have values of 1 with the following observations from the restatement4 dataset:

DV 1

1

7

11

DV 2

1

2

3

4

7

8

9

11

DV 3

6

10

12

Please let me know how I should code this.

God bless, best regards, and thanks a lot,

Jadallah

[jjadall1 · Accepted solution]

I got it to work:

data Sas_data.restatement5;
set SAS_data.restatement4;
if y1 = fyear then FO=1;
else FO=0;
run;

data SAS_data.restatement6;
set SAS_data.restatement5;
if y3 = fyear then AD=1;
else AD=0;
run;

data SAS_data.restatement7;
set SAS_data.restatement6;
if y1 le fyear le y2 then AO=1;
else AO=0;
run;

The first dataset (restatement5) is for DV1.  The second dataset (restatement6) is for DV3.  The third dataset (restatement7) is for DV2.

[jjadall1]

That code above works if you have 12/31 year-ends ONLY.  If there are some fiscal year-ends, use the following:

data Etr.restatement1;
set Etr.restatement;
rpb = restated_period_begin;
rpe = restated_period_ended;
RstateDays = (Datdif (rpb, rpe, 'act/act'))+1;
if month(restated_period_begin) le fiscal_month_end then y1 = year(restated_period_begin);
else y1 = year(restated_period_begin) + 1;
if month(restated_period_ended) le fiscal_month_end then y2 = year(restated_period_ended);
else y2 = year(restated_period_ended) + 1;
if month(disclosure_date) le fiscal_month_end then y3 = year(disclosure_date);
else y3 = year(disclosure_date) + 1;
run;

data Etr.restatement1a;
set Etr.restatement1;
If RstateDays LT 350 then delete;
run;

data Etr.restatement2;
set Etr.restatement1a;
drop rpb;
drop rpe;
run;

proc sort data=Etr.restatement2 out=Etr.restatement2a;
by cik;
run;

/*create observation for each year restated [include begin year and end year] */
data Etr.restatement3;
set Etr.restatement2a;
by cik;
if cik then do;
do fyear=y1 to y3;
output;
end;
end;
run;

proc sort data= Etr.restatement3 out= Etr.restatement4 noduplicate;
by restatement_key cik fyear;
run;

data Etr.restatement5;
set Etr.restatement4;
if y1 = fyear then FO=1;
else FO=0;
run;

data Etr.restatement6;
set Etr.restatement5;
if y3 = fyear then AD=1;
else AD=0;
run;

data Etr.restatement7;
set Etr.restatement6;
if y1 le fyear le y2 then AO=1;
else AO=0;
run;