DATA Step, Macro, Functions and more

3 Versions of Dependent Variable in Same Dataset

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

3 Versions of Dependent Variable in Same Dataset

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.

 

  1. 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.

 

  1. 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


Accepted Solutions
Solution
‎10-17-2017 10:48 AM
Contributor
Posts: 52

Re: 3 Versions of Dependent Variable in Same Dataset

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 149

Re: 3 Versions of Dependent Variable in Same Dataset

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

Re: 3 Versions of Dependent Variable in Same Dataset

Posted in reply to error_prone

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

Attachment
Attachment
Solution
‎10-17-2017 10:48 AM
Contributor
Posts: 52

Re: 3 Versions of Dependent Variable in Same Dataset

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.

Contributor
Posts: 52

Re: 3 Versions of Dependent Variable in Same Dataset

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 96 views
  • 0 likes
  • 2 in conversation